SQL 101

Counting Conditionally in SQL

When preparing a report on the number of customers you have, it can be helpful to split out the premium customers — i.e., those who spend more than $100/month — from the rest of the group.

Yet putting both premium customers and all customers in a single SQL query can be tricky. In this post, we’ll show you how.

Let’s start with a simple graph of customers:

select date(created_at), count(1)

from customers

groupby1

The familiar line going up and to the right is very satisfying:

Similarly, it’s easy enough to introduce a where clause to count only the premium customers:

select date(created_at), count(1)

from customers

where monthly_plan_amount >100

groupby1

This gets us a more modest, but still impressive graph to share with the team:

Yet the most effective presentation would have them both in the same graph, created by the same query, so they’re directly comparable over time.

We can do that by putting the conditional in a case statement, and putting that case statement inside a sum, like so:

select date(created_at),

sum(

case

when monthly_plan_amount >100then 1

else0

end

) as"Premium Customers",

count(1) as"All Customers"

from customers

groupby1

For each row, the case statement will return 1 if monthly_plan_amount is > 100, and 0 otherwise. The sum of those rows will equal the number of rows where the condition was true — in this case, the number of premium customers!

Meanwhile, since there’s no where clause on the whole query, we can still use count(1) for the total customer count.

Now we can compare not only absolute numbers, but the growth rate of each customer segment over time.

Next time you need to count based on a condition, try using sum(case...) to do it quickly and easily!