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!

Access IIF statement
CASE statement

About these ads

Posted on April 8, 2010, in SQL Server. Bookmark the permalink. 1 Comment.

  1. 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

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: