SQL: CASE WHEN turns rows into columns

A CASE inside an aggregate counts a subset, so one pass over the table can produce several conditional totals instead of one query each.

Code

SELECT
  COUNT(*)                                        AS total,
  SUM(CASE WHEN status = 'PAID'    THEN 1 ELSE 0 END) AS paid,
  SUM(CASE WHEN status = 'REFUND'  THEN 1 ELSE 0 END) AS refunded,
  SUM(CASE WHEN total > 1000 THEN total ELSE 0 END)   AS big_value
  FROM orders;

-- PostgreSQL has a shorter spelling:
SELECT COUNT(*) FILTER (WHERE status = 'PAID') AS paid FROM orders;

Output

total | paid | refunded | big_value
 9876 | 7210 |      118 |  482,110

-- One scan for four numbers. Four separate queries would read the table
-- four times.

Run this yourself in the Online Java Compiler, spin up a live REST API in the API Sandbox, or practise with Java interview questions.

Published 2026-08-25