Columns That Add Up to the Total

Concepts covered: Conditional Aggregation, Null Handling, Three-Valued Logic, Conditional Logic

A question that asks for active and inactive customers per region, plus the total, carries an unstated requirement: active plus inactive should equal the total. Columns built to split a total need 2 properties. Every row has to land in some column, so the conditions are exhaustive, and no row can land in 2 columns, so the conditions are exclusive. A CASE per column gives you neither for free, and the interviewer who asks 'do these add up?' is checking whether you know that.

The row that lands nowhere

A customer whose status is NULL breaks the first property. The comparison status = 'active' evaluates to NULL rather than false, CASE treats anything other than true as not matching, and with no ELSE the row produces NULL for every status column. COUNT(*) counts the row anyway. The same happens to a value nobody wrote a column for: a churned customer in a query with only active and inactive columns is in the total and in no slice. The run below has 1 of each.

SELECT
  region,
  COUNT(*) AS total_customers,
  COUNT(CASE WHEN status = 'active' THEN 1 END) AS active_count,
  COUNT(CASE WHEN status = 'inactive' THEN 1 END) AS inactive_count
FROM customers
GROUP BY region

In North, customer 1 is active, customer 2 has no status at all and customer 3 is inactive. The total is 3, the active count is 1 and the inactive count is 1, so the slices sum to 2 and a row is missing. In South, customer 4 is active and customer 6 inactive, but customer 5 has churned, so the slices again sum to 2 against a total of 3, with a different cause: the churned customer had nowhere to go. A reader of the dashboard would see the gap and ask where the third customer went, and the answer in production is usually a status that was never set or a value added after the query was written.

SELECT
  region,
  COUNT(*)                                                  AS total_customers,
  COUNT(CASE WHEN status = 'active'   THEN 1 END)           AS active_count,
  COUNT(CASE WHEN status = 'inactive' THEN 1 END)           AS inactive_count,
  COUNT(CASE WHEN status IS NULL
          OR status NOT IN ('active', 'inactive') THEN 1 END) AS other_count
FROM customers
GROUP BY region;

The other_count condition needs both halves. status NOT IN ('active', 'inactive') is NULL when status is NULL, so on its own it would skip the very row it exists to catch; the IS NULL test is what picks that row up. With the catch-all in place, both regions show a total of 3 and a 1 in each slice column, so the slices add back up to the total.

The row that lands twice

Overlapping conditions break the second property. Buckets such as 'under 100', '100 or more' and 'over 500' look like a partition, but a 600-dollar order satisfies both of the last 2 conditions and is counted in each, so the columns sum to more than the total. The same thing happens when one column counts premium orders and another counts North orders. Those columns slice on different attributes, so they're segments rather than a split and should never be expected to add up.

The run has 4 orders, and the 3 columns sum to 5. The fix is to make the ranges disjoint, amount >= 100 AND amount <= 500 for the middle bucket, or to assign each row one label first and count the labels.

One CASE, one label per row

WITH labeled AS (
  SELECT
    region,
    CASE
      WHEN status = 'active'   THEN 'active'
      WHEN status = 'inactive' THEN 'inactive'
      ELSE 'other'
    END AS bucket
  FROM customers
)
SELECT
  region,
  COUNT(*)                                         AS total_customers,
  COUNT(CASE WHEN bucket = 'active'   THEN 1 END)  AS active_count,
  COUNT(CASE WHEN bucket = 'inactive' THEN 1 END)  AS inactive_count,
  COUNT(CASE WHEN bucket = 'other'    THEN 1 END)  AS other_count
FROM labeled
GROUP BY region;

A CASE with several WHEN branches evaluates them in order and stops at the first true one, and the ELSE catches whatever remains, including the NULL status. That gives every row exactly one label by construction, so the counts over the labels are exhaustive and exclusive without any further checking. The 2-step shape costs a CTE, and it pays for itself the moment a fourth status value appears in the data: the new value falls into other and the total still reconciles, rather than vanishing from a dashboard.

About this lesson

This section is part of the Conditional Aggregation: Beginner lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.