HOW TO GET CONDITIONAL SUM AGGREGATES IN A RECORDSET USING THE CASE STATEMENT
Today I was presented with a request to find a SQL Server-compatible substitute for the IIF function that MS Access uses. The IIF function is a conditional function; it evaluates a condition (test for TRUE or FALSE) and returns one configurable value or another depending on the result of the evaluation. SQL Server does not have the IIF function, but it can easily perform the same operations using a CASE statement. The scenario I was shown today used the IIF function wrapped in a SUM function, effectively adding a ’1′ for each record having a certain value in the evaluated field.
To demonstrate how SQL Server can solve this problem, I’m going to use the AdventureWorks database and the Production.Product table. This table has a Color field. I want to find (in one query) the number of records for each respective color (or no color: NULL). Here’s a quick shot of part of the table:
I can see that there are the colors Black and Silver in the table, along with many NULLs. To get the count of records having the color Black, I can run the following:
That gives me a count of 93 records. But how many different colors are there? To find out, I’ll get all the DISTINCT Color values:
NOW I can formulate my entire query accurately, getting a total count, NULLS count, and counts for every color:
Looks good!
Posted on April 8, 2010, in SQL Server. Bookmark the permalink. 1 Comment.
development and administration, he enjoys C#.NET and Linux development.
Faster would be to use the PIVOT function in T-SQL
select *
from (select
isnull(Color, ‘NULL’) as Color
from production.product
)product
pivot(count(Color) For Color in
([Black],
[Blue],
[Grey],
[Multi],
[Red],
[Silver],
[Silver/Black],
[White],
[Yellow],
[NULL]
)
)p