Periscope Data
The world’s fastest visualization platform for data analysts.

Counting Conditionally in SQL

May 12, 2014

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
group by 1

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
group by 1

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), 
    when monthly_plan_amount > 100 then 1
    else 0 
) as "Premium Customers",
count(1) as "All Customers"
from customers
group by 1

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!

Haven't tried Periscope Data yet?
Start a trial and we’ll send you one of our famous coffee mugs.
Read More
Haven’t tried Periscope Data yet?