05 — Aggregation & GROUP BY

SELECT
  COUNT(*) AS total_orders,                    -- all rows, including any with NULL amounts
  COUNT(DISTINCT customer_id) AS unique_customers,
  SUM(amount) AS revenue,                      -- NULLs skipped; NULL if no rows
  AVG(amount) AS avg_order,                    -- NULLs skipped — not treated as 0
  MIN(amount) AS smallest,
  MAX(amount) AS largest
FROM orders;
--  total_orders | unique_customers | revenue | avg_order | smallest | largest
--  -------------+------------------+---------+----------+----------+---------
--           4   |        3         |  568.75 |  142.19  |   7.25   |  450.00

-- COUNT(*): counts ALL rows, including those where every column is NULL
-- COUNT(col): counts rows where col IS NOT NULL — skips NULLs in that column
-- COUNT(1): the constant 1 is never NULL, so it behaves like COUNT(*)
--   (The myth that COUNT(1) is faster is false — the planner optimizes both identically.)
SELECT
  COUNT(*) AS total,           -- 3 (all customers)
  COUNT(city) AS with_city,    -- 2 (Carol has city = NULL, skipped)
  COUNT(1) AS count_one        -- 3 (same as COUNT(*))
FROM customers;
--  total | with_city | count_one
--  ------+-----------+----------
--     3  |     2     |    3

-- Total spend per customer — one output row per customer_id group
SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
ORDER BY total_spent DESC;

-- Order count and average per city — groups are defined by c.city
SELECT c.city, COUNT(*) AS order_count, AVG(o.amount) AS avg_amount
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.city;

-- ❌ ERROR: column "o.amount" must appear in GROUP BY or be used in an aggregate
-- When rows collapse into groups, which amount should appear for a 5-row group? Undefined.
SELECT customer_id, amount FROM orders GROUP BY customer_id;

-- ✅ amount is aggregated — one value per group (the sum)
SELECT customer_id, SUM(amount) FROM orders GROUP BY customer_id;

-- ✅ amount is in GROUP BY — gives per-(customer, amount) groups (usually not intended)
SELECT customer_id, amount FROM orders GROUP BY customer_id, amount;

-- OK in PostgreSQL: c.id is the PK of customers, so c.name is functionally dependent on c.id
-- Standard SQL feature T301 — but not universally supported (MySQL's ONLY_FULL_GROUP_BY enforces it)
SELECT c.id, c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.id;   -- c.name not listed, but allowed because c.id is the PK → c.name is determined

-- Customers who spent more than $100 total — HAVING filters on the aggregate
SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id
HAVING SUM(amount) > 100;

-- Customers with at least 3 orders, placed after 2024-01-01
SELECT customer_id, COUNT(*) AS n
FROM orders
WHERE ordered_on >= '2024-01-01'   -- row filter (before grouping — shrinks input)
GROUP BY customer_id
HAVING COUNT(*) >= 3;              -- group filter (after grouping — filters output groups)

-- Orders per customer per year — group for each unique (customer_id, yr) combination
SELECT
  customer_id,
  EXTRACT(YEAR FROM ordered_on) AS yr,
  COUNT(*) AS n
FROM orders
GROUP BY customer_id, EXTRACT(YEAR FROM ordered_on)
ORDER BY customer_id, yr;

-- Sales by (city, year), plus subtotals by city, by year, and a grand total — all in one query
SELECT
  city,
  EXTRACT(YEAR FROM ordered_on) AS yr,
  SUM(amount) AS total
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY GROUPING SETS (
  (city, yr),   -- each city-year combo
  (city),       -- subtotal per city (across all years) — yr column is NULL
  (yr),         -- subtotal per year (across all cities) — city column is NULL
  ()            -- grand total — both city and yr are NULL
);

-- GROUPING(col) returns 1 when col is a subtotal (NULL due to grouping), 0 otherwise
SELECT
  CASE WHEN GROUPING(city) = 1 THEN 'ALL CITIES' ELSE city END AS city,
  CASE WHEN GROUPING(yr)   = 1 THEN 'ALL YEARS'  ELSE yr::text END AS yr,
  SUM(amount) AS total
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY GROUPING SETS ((city, yr), (city), (yr), ())
ORDER BY city, yr;

-- ROLLUP (city, yr) = GROUPING SETS ((city, yr), (city), ())
-- Produces subtotals at each level of the hierarchy: city+yr → city → grand total
SELECT city, yr, SUM(amount) AS total
FROM sales
GROUP BY ROLLUP (city, yr);

-- CUBE (city, yr) = GROUPING SETS ((city, yr), (city), (yr), ())
-- Produces every combination of subtotals — a full cross-tab
SELECT city, yr, SUM(amount) AS total
FROM sales
GROUP BY CUBE (city, yr);

-- A multi-level sales report with subtotals by city, by year, and a grand total.
-- Uses GROUPING SETS to produce all levels in a single pass over the data.
SELECT
  CASE WHEN GROUPING(c.city) = 1 THEN '=== ALL CITIES ===' ELSE c.city END AS city,
  CASE WHEN GROUPING(yr)     = 1 THEN '=== ALL YEARS ==='  ELSE yr::text   END AS yr,
  COUNT(*) AS order_count,
  SUM(o.amount) AS revenue,
  AVG(o.amount) AS avg_order
FROM customers c
JOIN orders o ON c.id = o.customer_id
CROSS JOIN LATERAL (SELECT EXTRACT(YEAR FROM o.ordered_on) AS yr) x
GROUP BY GROUPING SETS (
  (c.city, yr),    -- per city per year
  (c.city),        -- per city (all years)
  (yr),            -- per year (all cities)
  ()               -- grand total
)
ORDER BY
  CASE WHEN GROUPING(c.city) = 1 THEN 1 ELSE 0 END,   -- city subtotals after detail rows
  c.city NULLS LAST,
  CASE WHEN GROUPING(yr)     = 1 THEN 1 ELSE 0 END,
  yr NULLS LAST;

-- No GROUP BY → the entire table is one group → always returns exactly one row
SELECT SUM(amount) FROM orders;   -- one row: the total of all orders

-- Even on an empty table: returns one row with SUM = NULL (not zero rows!)
SELECT SUM(amount) FROM orders WHERE FALSE;   -- returns NULL, not 0, not zero rows

-- To get zero rows from an empty table, add a HAVING that's false:
SELECT SUM(amount) FROM orders HAVING COUNT(*) > 0;   -- zero rows if table is empty

-- ❌ WRONG: AVG over a join computes per-ORDER average, not per-CUSTOMER average
-- If Alice (NYC) has 1 order of $100 and Bob (NYC) has 10 orders averaging $20,
-- this reports NYC's average as (100 + 200) / 11 = $27.27 (per-order)
-- — NOT the average customer spend ($100 vs $200 → $150)
SELECT c.city, AVG(o.amount) AS avg_amount
FROM customers c
JOIN orders o ON c.id = o.customer_id
GROUP BY c.city;

-- ✅ RIGHT: nest the aggregate — first compute per-customer totals, then average those
SELECT c.city, AVG(customer_total) AS avg_customer_spend
FROM (
  SELECT c.city, c.id, SUM(o.amount) AS customer_total    -- level 1: per-customer total
  FROM customers c
  JOIN orders o ON c.id = o.customer_id
  GROUP BY c.city, c.id
) sub
GROUP BY c.city;   -- level 2: average of per-customer totals

-- Exact distinct count — O(N) memory, precise
SELECT COUNT(DISTINCT customer_id) FROM orders;

-- Approximate distinct count with HyperLogLog — O(1) memory, ~1-2% error
-- Requires: CREATE EXTENSION hll;
SELECT hll_cardinality(hll_agg(customer_id)) FROM orders;

-- Conditional aggregation with FILTER (PostgreSQL 9.4+) — clean and fast
SELECT
  customer_id,
  COUNT(*) AS total_orders,
  COUNT(*) FILTER (WHERE status = 'shipped') AS shipped,
  COUNT(*) FILTER (WHERE status = 'cancelled') AS cancelled,
  SUM(amount) FILTER (WHERE status = 'shipped') AS shipped_revenue
FROM orders
GROUP BY customer_id;

-- Portable equivalent using CASE
SELECT
  customer_id,
  COUNT(*) AS total_orders,
  SUM(CASE WHEN status = 'shipped' THEN 1 ELSE 0 END) AS shipped,
  SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM orders
GROUP BY customer_id;

SELECT c.city, AVG(o.amount) AS avg_amount
FROM customers c
LEFT JOIN orders o ON c.id = o.customer_id
GROUP BY c.city;