SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 My count query is not working
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

jatrix32
Starting Member

49 Posts

Posted - 09/08/2010 :  09:28:16  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 09/08/2010 :  10:01:43  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
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 - 09/08/2010 :  13:17:22  Show Profile  Reply with Quote
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

USA
36601 Posts

Posted - 09/08/2010 :  13:19:56  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 - 09/08/2010 :  13:56:25  Show Profile  Reply with Quote
This worked! Thank You !!!
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36601 Posts

Posted - 09/08/2010 :  14:00:17  Show Profile  Visit tkizer's Homepage  Reply with Quote


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

Subscribe to my blog
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000