Help on count - 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

Help on count

Author

Topic

whatamouth
Starting Member

16 Posts

Posted - 2012-02-16 : 15:12:33

Heres my reference table
Table_A
FK_____Column_A_____Column_B_____Column_C_____
001_____ok_____ok_____ok_____
002_____AA_____ok_____AA_____
003_____AA_____ok_____ok_____
004_____ok_____ok_____ok_____
005_____ok_____ok_____ok_____
001_____AA_____ok_____ok_____
002_____AA_____ok_____AA_____
003_____AA_____ok_____AA_____
004_____ok_____ok_____ok_____
005_____ok_____ok_____ok_____

Desired output = get the total count of 'AA' per column per
FK
So the output would be like this.

FK_____Column_A_____Column_B_____Column_C_____
001_____1_____0_____0_____
002_____2_____0_____2_____
003_____2_____0_____1_____
004_____0_____0_____0_____
005_____0_____0_____0_____

I can do this for 2 columns, dunno how for 3 or more

any help would be appreciated. Thanks

Neil Matias
neilmatias@yahoo.com

X002548
Not Just a Number

15586 Posts

Posted - 2012-02-16 : 15:17:08

SELECT FK
, SUM(CASE WHEN Column_A = 'AA' THEN 1 ELSE 0 END) AS Column_A
, SUM(CASE WHEN Column_B = 'AA' THEN 1 ELSE 0 END) AS Column_B
, SUM(CASE WHEN Column_C = 'AA' THEN 1 ELSE 0 END) AS Column_C
,ect
FROM Table_A
GROUP BY FK

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

whatamouth
Starting Member

16 Posts

Posted - 2012-02-16 : 15:53:05

Perfect

Thanks 8rett!

Neil Matias
neilmatias@yahoo.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources