Using SQL count in a case statement

Benchmarking with conditional aggregates

Aggregate functions like COUNT gain a significant edge when utilized with a CASE statement, allowing for precise analysis based on specific conditions.

Accounting for all scenarios

Use the ELSE clause in your CASE statement:

SELECT
  COUNT(CASE 
          WHEN order_status = 'Shipped' THEN 1 
          ELSE 0 -- Even if it's not shipped, we still recognize its existence 😉
        END) AS ShippedOrdersCount
FROM orders;


By using ELSE 0, we make sure all rows are included in our count, even those with NULL or undesirable values.

Avoiding nested queries

Try to avoid nesting your COUNT(CASE WHEN...) within another query. It's better for readability, debugging, and efficiency to adapt your initial structure for conditional counting.

Clarifying column aliases

Use discernible aliases to provide a clear lookup for your columns:

SELECT
  COUNT(CASE WHEN customer_type = 'New' THEN 1 END) AS Newbies,
  COUNT(CASE WHEN customer_type = 'Returning' THEN 1 END) AS FamiliarFaces
FROM customers;


Digging deeper with the COUNT function

Understanding the intricacies of SQL functions takes your queries from good to great. Let's proceed by exploring the specifics of using COUNT.

Counting unique occurrences

If a count of unique values is what you need, deploy the DISTINCT keyword:

SELECT
  COUNT(DISTINCT customer_id) AS UniqueCustomerCount
FROM orders;


This ascertains that every customer is counted only once and prevents duplication from skewing your results.

Dealing with the 'ALL' keyword

Beware COUNT uses the ALL keyword by default, considering all applicable values, including duplicates. Minding this behavior helps to understand when your counts may be unexpectedly high.

Using SUM for complex conditions

Sometimes a simple COUNT won't cut it, and you need something more nuanced. Enter SUM:

SELECT
  SUM(CASE WHEN year = 2021 THEN amount ELSE 0 END) AS TotalSales2021
FROM sales;


Here we are not just counting orders, but calculating the total sales for the year 2021. Another feather in your SQL wizard hat! 🧙‍♂️🪄