SUM with CASE
Part of the Beyond the Basics section of Coddy's SQL journey — lesson 13 of 27.
The same idea works with SUM: and it's even more flexible. The CASE can return 1 for matching rows and 0 for everything else, or it can return any numeric value to weight rows differently.
SELECT
SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active_count,
SUM(CASE WHEN plan = 'pro' THEN amount ELSE 0 END) AS pro_revenue
FROM usersSUM(CASE … THEN 1 ELSE 0 END) and COUNT(CASE … THEN 1 END) give the same answer for counting; pick the style your team prefers.
Challenge
EasyAvailable tables and columns:
<strong>transactions</strong>:<strong>id</strong>,<strong>category</strong>,<strong>type</strong>,<strong>amount</strong>
type is either 'sale' or 'refund'. For each category return:
categorynet_revenue: sum of sale amounts minus sum of refund amounts
Compute net_revenue in a single SUM(CASE …) expression where refunds contribute their amount as a negative number. Order by net_revenue descending.
Cheat sheet
Use SUM with CASE to conditionally aggregate values — return 1/0 for counting, or actual values for weighted sums:
SELECT
SUM(CASE WHEN status = 'active' THEN 1 ELSE 0 END) AS active_count,
SUM(CASE WHEN plan = 'pro' THEN amount ELSE 0 END) AS pro_revenue
FROM usersSUM(CASE … THEN 1 ELSE 0 END) and COUNT(CASE … THEN 1 END) produce the same count result.
Try it yourself
SELECT category,
-- one SUM(CASE ...) where refunds count negative
FROM transactions
GROUP BY category
Test yourselfThis lesson includes a short quiz. Start the lesson to answer it and track your progress.