SQL Foo | Count within a case statement

Just the other day I was asked to help a peer solve an SQL problem they were having; they were trying to count within a case statement which was proving to be problematic. They had several columns within the query, most using an analytical function to find the sum, min or max of various fields – but there was one instance where she needed to count the number of occurrences of a situation, a situation that required several other fields to determine, and without adding those other fields to the GROUP BY would break the logic.

Counting like a ninja with SQL. — Like a ninja, count within a case statement.

How to count within a case statement?


SELECT
   OwnerUserId
  ,MAX(OwnerDisplayName)
  ,MAX(SCORE)
  ,CASE
    WHEN AcceptedAnswerId IS NOT NULL
     AND ViewCount > 10
    THEN COUNT(1)
   END) AS Foo
FROM Posts
GROUP BY OwnerUserId;

SELECT

OwnerUserId

,MAX(OwnerDisplayName)

,MAX(SCORE)

,CASE

WHEN AcceptedAnswerId IS NOT NULL

AND ViewCount > 10

THEN COUNT(1)

END) AS Foo

FROM Posts

GROUP BY OwnerUserId;

Take a look at the problematic example of a presumably correct way to count within a case statement. This query, however, will give an error because the two columns used within the case statement are not part of the aggregated GROUP BY clause. If you were to add “AcceptedAnswerId” and “ViewCount” to the GROUP BY, then this would work (on some RDBMSs) – except it wouldn’t give you the result set you’re looking for.

There is a trick, though. Let’s take a look:


SELECT
   OwnerUserId
  ,MAX(OwnerDisplayName)
  ,MAX(SCORE)
  ,SUM(CASE
        WHEN AcceptedAnswerId IS NOT NULL
         AND ViewCount > 10
        THEN 1
        ELSE 0
       END) AS Foo
FROM Posts
GROUP BY OwnerUserId;

SELECT

OwnerUserId

,MAX(OwnerDisplayName)

,MAX(SCORE)

,SUM(CASE

WHEN AcceptedAnswerId IS NOT NULL

AND ViewCount > 10

THEN 1

ELSE 0

END) AS Foo

FROM Posts

GROUP BY OwnerUserId;

This is how you correctly count within a case statement! Notice that we’ve simply wrapped the case statement with a SUM function and provided the sum with a 1 or a 0. This gives the same result that a COUNT would without compromising your result set.

Find this helpful? Leave a comment!