Aggregation & grouping

CREATE TABLE r (score INT);
INSERT INTO r VALUES (10),(20),(NULL),(NULL);

SELECT COUNT(*)                 AS rows_,       -- 4
       COUNT(score)             AS scored,      -- 2
       SUM(score)               AS total,       -- 30
       AVG(score)               AS avg_,        -- 15.0
       AVG(COALESCE(score, 0))  AS avg_zero     -- 7.5
FROM r;

-- the empty-set asymmetry
SELECT COUNT(*), SUM(score), AVG(score)
  FROM r WHERE score > 1000;
--   0, NULL, NULL   ← COUNT gives 0, the others give NULL

-- so this silently produces NULL, not 100:
SELECT 100 + SUM(score) FROM r WHERE score > 1000;   -- NULL
SELECT 100 + COALESCE(SUM(score), 0) FROM r WHERE score > 1000;  -- 100

-- ambiguous: which name? the group may contain many.
SELECT dept, name, COUNT(*) FROM emp GROUP BY dept;   -- ERROR
-- unambiguous: pick one explicitly
SELECT dept, MAX(name), COUNT(*) FROM emp GROUP BY dept;
SELECT dept, ANY_VALUE(name), COUNT(*) FROM emp GROUP BY dept;

-- legal: grouping by the PK determines name and email
SELECT u.id, u.name, u.email, COUNT(o.id)
  FROM users u LEFT JOIN orders o ON o.user_id = u.id
 GROUP BY u.id;
--   Postgres and MySQL 8 both accept this.
--   Grouping by a non-key column would not be accepted.

-- WRONG PLACE: groups every department, then throws most away
SELECT dept, COUNT(*) FROM emp
 GROUP BY dept
HAVING dept <> 'temp';          -- filters AFTER aggregating
-- RIGHT: excludes the rows before any grouping work happens
SELECT dept, COUNT(*) FROM emp
 WHERE dept <> 'temp'
GROUP BY dept;

-- both together: the normal shape
SELECT dept, COUNT(*) FROM emp
 WHERE hired_at >= '2026-01-01' -- which rows to consider
GROUP BY dept
HAVING COUNT(*) > 5;               -- which groups to keep

-- GROUPING SETS: name exactly the groupings you want
SELECT region, product, SUM(amount)
  FROM sales
 GROUP BY GROUPING SETS (
   (region, product),   -- detail
   (region),            -- subtotal per region
   ()                   -- grand total
 );

-- ROLLUP: the hierarchical shortcut for exactly that pattern
SELECT region, product, SUM(amount)
  FROM sales GROUP BY ROLLUP (region, product);
--   (region, product), (region), ()
-- MySQL's older spelling of ROLLUP:
--   GROUP BY region, product WITH ROLLUP
-- CUBE: every combination (Postgres; MySQL has no CUBE)
SELECT region, product, SUM(amount)
  FROM sales GROUP BY CUBE (region, product);
--   (region,product), (region), (product), ()

SELECT
CASE WHEN GROUPING(region) = 1 THEN 'ALL REGIONS'
ELSE COALESCE(region, '(unknown)') END AS region,
  SUM(amount)
FROM sales GROUP BY ROLLUP (region);

-- Postgres, SQLite, standard
SELECT
COUNT(*)                                    AS total,
  COUNT(*) FILTER (WHERE status = 'paid')     AS paid,
  COUNT(*) FILTER (WHERE status = 'refunded') AS refunded,
  SUM(amount) FILTER (WHERE status = 'paid')   AS revenue
FROM orders;

-- MySQL: CASE returning NULL for non-matching rows
SELECT
COUNT(*)                                              AS total,
  COUNT(CASE WHEN status = 'paid' THEN 1 END)        AS paid,
  SUM(CASE WHEN status = 'paid' THEN amount END)     AS revenue,
  SUM(status = 'paid')                                AS paid_short
FROM orders;
--   note: no ELSE, so non-matching rows yield NULL and are skipped.
--   SUM(boolean) works in MySQL because TRUE is 1.

-- from: one row per (month, region)
-- to:   one row per month, one COLUMN per region
SELECT
  month,
  SUM(CASE WHEN region = 'EU' THEN amount ELSE 0 END) AS eu,
  SUM(CASE WHEN region = 'US' THEN amount ELSE 0 END) AS us,
  SUM(CASE WHEN region = 'AF' THEN amount ELSE 0 END) AS af,
  SUM(amount) AS total
FROM sales
GROUP BY month
ORDER BY month;

-- exact, and it scans
SELECT COUNT(*) FROM big_table;

-- approximate, and instant
SELECT table_rows FROM information_schema.tables
 WHERE table_schema = DATABASE() AND table_name = 'big_table';

-- Postgres equivalent
--   SELECT reltuples::bigint FROM pg_class WHERE relname = 'big_table';

-- the three counts differing on the same column
SELECT COUNT(*), COUNT(region), COUNT(DISTINCT region) FROM sales;

-- Postgres / Oracle / standard
SELECT
PERCENTILE_CONT(0.5)  WITHIN GROUP (ORDER BY ms) AS p50,
  PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY ms) AS p95,
  PERCENTILE_DISC(0.95) WITHIN GROUP (ORDER BY ms) AS p95_actual
FROM requests;
--   CONT interpolates between values; DISC returns an actual
--   observed value. For latency SLOs, DISC is usually what you want.

-- MySQL: p95 latency
SELECT ms AS p95 FROM (
  SELECT ms,
         PERCENT_RANK() OVER (ORDER BY ms) AS pr
    FROM requests
) t
WHERE pr >= 0.95
ORDER BY pr
LIMIT 1;