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?

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:

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!