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
 Help with multiple COUNT() script
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqlslick
Yak Posting Veteran

83 Posts

Posted - 03/10/2014 :  14:03:06  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

Pakistan
1054 Posts

Posted - 03/10/2014 :  14:19:02  Show Profile  Reply with Quote
Does this work?


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

Cheers
MIK
Go to Top of Page

sqlslick
Yak Posting Veteran

83 Posts

Posted - 03/10/2014 :  15:06:46  Show Profile  Reply with Quote
Yes, that is perfect, thank you so much!!!
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