Selective Count statements - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Selective Count statements

Author

Topic

Natase
Starting Member

3 Posts

Posted - 2005-02-01 : 03:40:32

Hi all,

I have a field called "type" in one of my tables and am trying to produce a report of how many of each distinct type there is.

I've got the rest of the query running perfectly. I'm at the stage where I have everything grouped by month, then by client, then I have a field that has a count of "type". Obviously this just gives a basic count of each row that has a type.

SELECT MONTH([cf].[date_received]), [clients].[abbr], COUNT([cf].[type]) FROM [cf] JOIN [clients] ON [clients].[ID] = [cf].[client_id] GROUP BY MONTH([cf].[date_received]), [clients].[abbr], [cf].[type]

I've tried all sorts of different things like a WHERE clause for the count (to retreive a count of a specific type). Nothing seems to work. Any ideas of what I could try? This seems like it would be something people would use fairly often so I'm guessing the answer is out there. I just can't find it for the life of me.

AndyB13
Aged Yak Warrior

583 Posts

Posted - 2005-02-01 : 03:45:53

Try COUNT(DISTINCT [cf].[type])

If thats not what you want post sample data and what your expected results are

Natase
Starting Member

3 Posts

Posted - 2005-02-01 : 19:46:31

I'm guessing that would give me the number of different types.

I'll try to explain myself better;

The field in question (type) can contain 'a', 'b', or 'c'.
I want the results to display something like this.

Month : Client : A : B : C :
-----------------------------------------------------
Jan : client1 : 0 : 1 : 16 :
Jan : client2 : 12 : 3 : 9 :
Feb : client1 : 2 : 0 : 12 :

Is there any way to do this in a single query or do I have to do 3 queries and place a where clause on the 'type' field.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2005-02-01 : 20:22:35

[code]
SELECT
MONTH([cf].[date_received]),
[clients].[abbr],
A = sum(case when [cf].[type]='A' then 1 else 0 end),
B = sum(case when [cf].[type]='B' then 1 else 0 end),
C = sum(case when [cf].[type]='C' then 1 else 0 end)
FROM [cf]
JOIN [clients]
ON [clients].[ID] = [cf].[client_id]
GROUP BY MONTH([cf].[date_received]), [clients].[abbr]
[/code]

Corey