count total - SQL Server Forums

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

count total

Author

Topic

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-04-23 : 03:10:54

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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-23 : 03:15:06

means it is grouping NULLs as one group... right?

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

--
Chandu

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-04-23 : 03:19:12

the result should be as below
Total Item
1 itemA
1 itemB
1 itemC
1 itemD
6 NULL

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-23 : 03:33:46

Can you post sample data and respective output for that data?

--
Chandu

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-04-23 : 03:45:05

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

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-23 : 04:21:51

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

peace
Constraint Violating Yak Guru

420 Posts

Posted - 2013-04-23 : 04:35:30

because it shared the same ID.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-23 : 05:06:55

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources