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
 count total
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

peace
Constraint Violating Yak Guru

373 Posts

Posted - 04/23/2013 :  03:10:54  Show Profile  Reply with Quote
how can i exclude the ID to be get the same count

total ID 10
ID 111 purchase itemA and itemB
ID 222 purchase itemC
ID 333 purchase itemD

i tried select COUNT(*),item from tableA group by item
but it return the total of 11

Total Item
1 itemA
1 itemB
1 itemC
1 itemD
7 NULL

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 04/23/2013 :  03:15:06  Show Profile  Reply with Quote
means it is grouping NULLs as one group... right?

Include
SELECT COUNT(*), item
FROM tableA
WHERE item IS NOT NULL
GROUP BY item

--
Chandu
Go to Top of Page

peace
Constraint Violating Yak Guru

373 Posts

Posted - 04/23/2013 :  03:19:12  Show Profile  Reply with Quote
the result should be as below
Total Item
1 itemA
1 itemB
1 itemC
1 itemD
6 NULL
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 04/23/2013 :  03:33:46  Show Profile  Reply with Quote
Can you post sample data and respective output for that data?

--
Chandu
Go to Top of Page

peace
Constraint Violating Yak Guru

373 Posts

Posted - 04/23/2013 :  03:45:05  Show Profile  Reply with Quote
i have list of data as below
total ID = 10
ID 111 purchase itemA and itemB
ID 222 purchase itemC
ID 333 purchase itemD
ID 444 purchase none
ID 555 purchase none
ID 666 purchase none
ID 777 purchase none
ID 888 purchase none
ID 999 purchase none
ID 100 purchase none

i would like to group them up. but total should be 10.

select COUNT(*),item from tableA group by item
but it return the total of 11 as below:

Total Item
1 itemA
1 itemB
1 itemC
1 itemD
7 NULL

Result should be as below:

Total Item
1 itemA
1 itemB
1 itemC
1 itemD
6 NULL
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 04/23/2013 :  04:21:51  Show Profile  Reply with Quote
how come total of NULL group 6? there are 7 IDs with Item=none
Under ID=111 there is two items( A and B).. so total count is 11

--
Chandu
Go to Top of Page

peace
Constraint Violating Yak Guru

373 Posts

Posted - 04/23/2013 :  04:35:30  Show Profile  Reply with Quote
because it shared the same ID.
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2168 Posts

Posted - 04/23/2013 :  05:06:55  Show Profile  Reply with Quote
See the following groups... Your query will group and then counts Number of IDs like this:
ItemA
------
ID 111
Total = 1

ItemB
------
ID 111
Total = 1

ItemC
------
ID 222
Total = 1

ItemD
------
ID 333
Total = 1

NULL (none)
------
ID 444 purchase none
ID 555 purchase none
ID 666 purchase none
ID 777 purchase none
ID 888 purchase none
ID 999 purchase none
ID 100 purchase none
Total = 7

--
Chandu
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.06 seconds. Powered By: Snitz Forums 2000