Help with multiple COUNT() script

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

Help with multiple COUNT() script

Author

Topic

sqlslick
Yak Posting Veteran

83 Posts

Posted - 2014-03-10 : 14:03:06

Hello,

Consider the following dataset:

COL1 | COL2 | COL3 | COL4
1 | FD | DR. A | Y
2 | FD | DR. A | Y
3 | FD | DR. A | N
4 | FD | DR. A | Y
5 | FD | DR. A | Y
6 | PF | DR. A | Y
7 | FD | DR. B | Y
8 | PF | DR. B | N

Consider the script below:

SELECT
COL2, COL3, COUNT(COL1) AS TOTALS
FROM CASES
GROUP BY COL2, COL3
ORDER BY COL3, COL2

The script above produces the following output:

COL2 | COL3 | TOTALS
FD | DR. A | 5
PF | DR. A | 1
FD | DR. B | 1
PF | DR. B | 1

I need to add one more column to the script that counts records with 'Y' in COL4 for each COL1 category (FD, PF). The final dataset would look like this:

COL2 | COL3 | TOTALS | NEWCOL
FD | DR. A | 5 | 4
PF | DR. A | 1 | 1
FD | DR. B | 1 | 1
PF | DR. B | 1 | 0

I am having a hard time trying to use COUNT() on multiple columns with the GROUP BY restrictions that exist. Your help will be immensely aprpeciated.

Thank you!!!

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2014-03-10 : 14:19:02

Does this work?

SELECT
COL2, COL3, COUNT(COL1) AS TOTALS
,SUM(CASE when Col4='Y' then 1 else 0 end) as NewCol

Cheers
MIK

sqlslick
Yak Posting Veteran

83 Posts

Posted - 2014-03-10 : 15:06:46

Yes, that is perfect, thank you so much!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources