Hopefully a simple COUNT function...

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

Hopefully a simple COUNT function...

Author

Topic

rickystyx
Starting Member

4 Posts

Posted - 2012-04-12 : 17:30:21

Hopefully a simple COUNT function is the answer here...

TABLE.ColumnA contains unique employee identifiers
TABLE.ColumnB contains value either 10 or 20

I want to select three columns:
Identifier, Count of 10, Count of 20

FROM TABLE
WHERE (criteria)
GROUP BY Identifier

--Any help would be appreciated!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-12 : 17:35:56

[code]
SELECT ColumnA,
COUNT(CASE WHEN ColumnB=10 THEN 1 END) AS [Cnt10],
COUNT(CASE WHEN ColumnB=20 THEN 1 END) AS [Cnt20]
FROM Table
GROUP BY ColumnA
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rickystyx
Starting Member

4 Posts

Posted - 2012-04-12 : 17:50:18

Perfect. Thanks so much!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-13 : 11:36:51

welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-04-17 : 04:28:08

quote:
Originally posted by visakh16

SELECT ColumnA,
COUNT(CASE WHEN ColumnB=10 THEN 1 END) AS [Cnt10],
COUNT(CASE WHEN ColumnB=20 THEN 1 END) AS [Cnt20]
FROM Table
GROUP BY ColumnA

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

The above may return warnings on NULLs. I prefer this

SELECT ColumnA,
SUM(CASE WHEN ColumnB=10 THEN 1 ELSE 0 END) AS [Cnt10],
SUM(CASE WHEN ColumnB=20 THEN 1 ELSE 0 END) AS [Cnt20]
FROM Table
GROUP BY ColumnA

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources