Conditional count and sum (COUNT/SUM with a condition)

Get totals and conditional subtotals in a single query. The portable trick is a CASE inside the aggregate — works in every dialect:

SELECT
  COUNT(*)                                          AS total_orders,
  SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END)  AS paid_orders,
  SUM(CASE WHEN status = 'paid' THEN amount END)    AS paid_revenue,
  AVG(CASE WHEN status = 'paid' THEN amount END)    AS avg_paid_order
FROM orders;

Cleaner, where supported — the SQL-standard FILTER clause:

-- PostgreSQL, SQLite 3.30+, DuckDB
SELECT
  COUNT(*)                                   AS total_orders,
  COUNT(*)    FILTER (WHERE status = 'paid')  AS paid_orders,
  SUM(amount) FILTER (WHERE status = 'paid')  AS paid_revenue
FROM orders;

Engine-specific shorthands for the common “count matching rows”:

SELECT COUNTIF(status = 'paid')  FROM orders;   -- BigQuery
SELECT COUNT_IF(status = 'paid') FROM orders;   -- Snowflake

COUNT vs SUM for a count. Use COUNT(CASE WHEN cond THEN 1 END) or SUM(CASE WHEN cond THEN 1 ELSE 0 END). Don’t write COUNT(CASE WHEN cond THEN 1 ELSE 0 END) — COUNT counts non-NULLs, so the ELSE 0 makes it count every row. With CASE and no ELSE, non-matching rows are NULL and excluded.