SQL Aggregate Advanced: Conditional SUM, AVG, COUNT

Why conditional aggregates beat self-joins

A common report asks for revenue by segment in columns: West, East, Online, Store. The naive way is four separate queries joined together, which is slow and noisy. A conditional aggregate computes all four columns in one pass by embedding a CASE WHEN inside the SUM. On the Orders table this turns ten lines of SQL into three and runs against the data once.

The trick is simple: the aggregate only counts rows where the inner condition is true, and the ELSE makes the rest contribute zero. Once that clicks, most pivot reports become one query.

Conditional SUM with CASE WHEN

SELECT SUM(CASE WHEN region = 'West' THEN amount ELSE 0 END) AS west_rev,
  SUM(CASE WHEN region = 'East' THEN amount ELSE 0 END) AS east_rev
FROM Orders

SELECT SUM(CASE WHEN region = 'West' THEN amount ELSE 0 END) AS west_rev
FROM Orders
WHERE order_date >= '2025-01-01'

SELECT SUM(amount) FILTER (
  WHERE region = 'West') AS west_rev,
  SUM(amount) FILTER (
  WHERE region = 'East') AS east_rev
FROM Orders

Conditional COUNT and AVG

SELECT COUNT(CASE WHEN status = 'late' THEN 1 END) AS late_orders
FROM Orders

SELECT AVG(CASE WHEN region = 'West' THEN amount END) AS west_avg
FROM Orders

SELECT COUNT(DISTINCT CASE WHEN region = 'West' THEN customer_id END) AS west_customers
FROM Orders

Building a pivot table with one query

SELECT product_id,
  SUM(CASE WHEN region = 'West' THEN amount ELSE 0 END) AS west,
  SUM(CASE WHEN region = 'East' THEN amount ELSE 0 END) AS east
FROM Orders
GROUP BY product_id

SELECT product_id,
  SUM(amount) AS total, SUM(CASE WHEN region = 'West' THEN amount ELSE 0 END) AS west
FROM Orders
GROUP BY product_id

SELECT product_id,
  SUM(CASE WHEN region = 'West' THEN amount ELSE 0 END) * 1.0 / SUM(amount)
  AS west_share
FROM Orders
GROUP BY product_id

COUNT DISTINCT and window aggregates

SELECT region, COUNT(DISTINCT customer_id) AS buyers
FROM Orders
GROUP BY region

SELECT order_date, SUM(amount) OVER (
  ORDER BY order_date) AS running_total
FROM Orders

The divide-by-zero trap

When you divide one aggregate by another, a group with zero rows makes the denominator zero and the whole expression errors or returns NULL. This is the most common failure in a share or ratio report, and it often shows up only for a quiet segment that has no data yet.

SELECT product_id,
  CASE WHEN SUM(amount)
  = 0 THEN 0
  ELSE SUM(CASE WHEN region='West' THEN amount ELSE 0 END) * 1.0 / SUM(amount)
  END AS west_share
FROM Orders
GROUP BY product_id

Worked example: regional revenue and share in one query

Here is a runnable query against Orders that returns each product's total, West revenue, and West share, all computed in a single pass.

SELECT product_id,
  SUM(amount) AS total,
  SUM(CASE WHEN region = 'West' THEN amount ELSE 0 END) AS west_rev,
  CASE WHEN SUM(amount)
  = 0 THEN 0
  ELSE SUM(CASE WHEN region = 'West' THEN amount ELSE 0 END) * 1.0 / SUM(amount)
  END AS west_share
FROM Orders
GROUP BY product_id
ORDER BY total DESC;

Conditional aggregates inside window functions

You can combine CASE with a window function to compute a conditional running total. SUM(CASE WHEN region = 'West' THEN amount ELSE 0 END) OVER (ORDER BY order_date) gives a running West revenue that keeps every row, so you can plot the segment's cumulative line without a self-join or a temp table.

The window version keeps the detail rows, while a plain GROUP BY collapses them into one summary row. A common mistake is forgetting the OVER clause, which turns the expression back into a single grand total for the whole table and loses the per-row line entirely. Always pair the conditional aggregate with an explicit window so the running calculation is clear.

SELECT order_date,
  SUM(CASE WHEN region = 'West' THEN amount ELSE 0 END) OVER (
  ORDER BY order_date) AS running_west
FROM Orders
ORDER BY order_date

Computing several metrics in one pass

A single SELECT can return the total, the West share, the East share, and the average ticket at once, which is far cheaper than running four separate queries against the Orders table. Stakeholders rarely want one number; they want the full scorecard, and conditional aggregates deliver it in a single scan of the data.

In my experience one combined query also prevents mismatched row counts between metrics, which happens when you run separate queries that each filter the data differently. Keep every metric in the same GROUP BY so the columns line up row for row. See our GROUP BY and HAVING guide for the grouping basics this pattern builds directly on.

SELECT product_id,
  SUM(amount) AS total,
  AVG(amount) AS avg_ticket,
  SUM(CASE WHEN region = 'West' THEN amount ELSE 0 END) * 1.0 / SUM(amount)
  AS west_share
FROM Orders
GROUP BY product_id
ORDER BY total DESC

Before you ship a conditional aggregate report, confirm the column totals add up to the grand total. A West share of 0.45 should pair with an East and Other share that sum to 1.0, and a missing ELSE in a COUNT can quietly drop rows so the segments no longer reconcile. I keep a final SELECT that sums the segment columns and compares them to the plain total, and I fail the query if they differ by more than a rounding cent. In my experience that one reconciliation step catches more logic errors than any code review, because the numbers are right in front of you and the mismatch is impossible to ignore once it is printed.

Next steps and a quick checklist

Use CASE WHEN inside SUM, COUNT, and AVG to build pivot columns in one query. Prefer AVG(CASE ...) over manual divides, multiply by 1.0 before a ratio, and guard every denominator with NULLIF or a CASE. Combine these with GROUP BY for the row dimension and with date aggregation when the segments are time periods. Practice on the Orders table until a five-query report collapses into a single clean SELECT. These column patterns sit on top of SQL GROUP BY and HAVING, and pair well with SQL date aggregation when the segments are time periods. Add SQL CASE WHEN labels before you sum to keep the logic readable.