Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 My count query is not working

Author  Topic 

jatrix32
Starting Member

49 Posts

Posted - 2010-09-08 : 09:28:16
I wrote a query that is supposed to tell me how many users have an email address in my table, based on their group and sub group.
I have a main group called 'group_id'.
There are many sub groups within this group called 'sub_group_id1', 'sub_group_id2' etc. All of this information resides within an account which is listed as 'account_id'.

So I the structure looks like this:

Acount_ID (integer)
Group_ID (comma separated numbers ex. 4,6,7)
sub_group_id1 (comma separated numbers ex. 4,6,7)
sub_group_id2
sub_group_id3

because I have many accounts in my table, I need to drill to this particular account, and then grab the group, and then the sub group and then check if the user has an email address.

Here is the query I have written:

SELECT COUNT(*) AS [Number of employees]
FROM SUBSCRIPTION
WHERE (account_id = 119) AND (group_id = '% 1 %') AND (sub_group_id = '% 5 %') AND (sub_email <> NULL)

My query is netting a record count of 0, and I know this is incorrect.

What am I doing wrong?

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2010-09-08 : 10:01:43
You're doing equality matches. The = ignores wildcards and looks for exact, literal matches. To use wildcards, you need LIKE.

Additionally, consider normalising this table. Comma-delimited lists in columns are a clear sign that the table is not normalised and an unnormalised design leads to difficult queries (as it has here)

--
Gail Shaw
SQL Server MVP
Go to Top of Page

jatrix32
Starting Member

49 Posts

Posted - 2010-09-08 : 13:17:22
I am still getting 0 results. I also had to check against an email address and a phone number. I want the results for all users that have registered email address (but does not have a registered phone number), that belong to account_id 19, in group 1, in sub_group LIKE 1.

Here is my query:

SELECT COUNT(*) AS Expr1
FROM SUBSCRIPTION
WHERE (account_id = 119) AND (group_id = 1) AND (sub_group_id LIKE '%1%') AND (sub_email <> NULL) AND (sub_user_number = NULL)

I should not be getting 0 results. Any help would be great.
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-08 : 13:19:56
Use IS NOT NULL, not <> NULL.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

jatrix32
Starting Member

49 Posts

Posted - 2010-09-08 : 13:56:25
This worked! Thank You !!!
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-08 : 14:00:17


Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
   

- Advertisement -