Friday, April 17, 2015

More Than One Aggregation criteria in same SQL Query

Microsoft SQL Server has a very nice feature  that you can write conditions inside the aggregation functions for example count( CASE WHEN Status=1 THEN 1 ELSE null END   ) 

with this feature we can do more that one feature in the same query

for example:
if we have table Person that has the following columns
Fname
Lname
Active

we can make the following Query to get number of active and number of inactive employees

select count( CASE WHEN Active=1 THEN 1 ELSE null END   )  as NUM_ACTIVE , count( CASE WHEN Active=0 THEN 1 ELSE null END   )  as NUM_INACTIVE from Person

No comments:

Post a Comment