Counting and Grouping with Multiple Criteria

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

Counting and Grouping with Multiple Criteria

Author

Topic

sqlslick
Yak Posting Veteran

83 Posts

Posted - 2014-07-14 : 11:19:01

Hi guys,

Consider the following dataset:

ID|MD|TYPE
1|JOHN|A
2|JOHN|B
3|JOHN|B
4|BOB|A
5|BOB|A
6|BOB|B
7|BOB|B
8|BOB|B

I need to count the number of IDs for each MD and each TYPE like this:

MD|A|B
JOHN|1|2
BOB|2|3

I only know how to count everything by MD like this:
SELECT MD, COUNT(ID) AS TOTAL
FROM MY_TABLE
GROUP BY MD
ORDER BY MD

The query above results in:

MD|TOTAL
JOHN|3
BOB|5

I know there's a trick tot his but can't remember how to accomplish. Your help will be extremely appreciated!

Thank you!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-07-14 : 11:30:29

Group by [TYPE] also.

SELECT MD, [TYPE] COUNT(ID) AS TOTAL
FROM MY_TABLE
GROUP BY MD,[TYPE]
ORDER BY MD

That is not quite what you want, but now you can pivot the results to get what you want

SELECT * FROM 
(
SELECT MD, [TYPE] COUNT(ID) AS TOTAL
FROM MY_TABLE
GROUP BY MD,[TYPE]
) s
PIVOT (MAX(Total) FOR [Type] IN ([a],[b]))P
	ORDER BY MD

sqlslick
Yak Posting Veteran

83 Posts

Posted - 2014-07-14 : 11:57:36

Hi James,

I wasn't expecting that but it works like a charm!

I should have asked this from the beginning but I'm not really familiar with this PIVOT function. How would I add a third column with the combined total for both A and B, like this:

MD|A|B|OVERALL
JOHN|1|2|3
BOB|2|3|5

Thanks in adavanced!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-07-14 : 12:35:23

Probably the easiest is to include that column in the outermost select

SELECT *, [A]+[B] as OVERALL FROM
(
......

In case there are situations where there is no [A] or no [B], you would want to do

SELECT *, COALESCE([A],0)+COALESCE([B],0) as OVERALL FROM
......

sqlslick
Yak Posting Veteran

83 Posts

Posted - 2014-07-14 : 13:35:02

That is pretty wicked ... thank you sir!

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-07-14 : 16:56:29

you are welcome, glad to help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources