Aggregation & Grouping — Cheat Sheet

GROUPING SETS

-- Requirement: total order amount grouped by city only,
-- by product category only, and by (city + category) together.

-- Anti-pattern: three scans with UNION ALL
SELECT city, NULL AS category, SUM(o.amount) AS revenue
FROM orders o
JOIN users u ON u.id = o.user_id
GROUP BY u.city

UNION ALL

SELECT NULL, p.category, SUM(o.amount)
FROM orders o
JOIN products p ON p.id = o.product_id
GROUP BY p.category

UNION ALL

SELECT u.city, p.category, SUM(o.amount)
FROM orders o
JOIN users u ON u.id = o.user_id
JOIN products p ON p.id = o.product_id
GROUP BY u.city, p.category;

-- Better: single scan with GROUPING SETS
SELECT
    u.city,
    p.category,
    SUM(o.amount) AS revenue
FROM orders o
JOIN users u ON u.id = o.user_id
JOIN products p ON p.id = o.product_id
GROUP BY GROUPING SETS (
    (u.city),          -- group 1: by city only
    (p.category),      -- group 2: by category only
    (u.city, p.category) -- group 3: both
);

DISTINCT vs GROUP BY

-- Equivalent: list distinct cities where we have users
SELECT DISTINCT city FROM users;
-- Same plan as:
SELECT city FROM users GROUP BY city;

-- EXPLAIN (PostgreSQL) — both produce:
-- HashAggregate  (cost=310.00..320.00 rows=1000)
--   Group Key: city
--   ->  Seq Scan on users

-- Rule of thumb: if you need only deduplication, DISTINCT is fine.
-- If you need aggregates, counts, or HAVING, use GROUP BY.

COUNT(DISTINCT col) & Approximate Counting

-- NULL behaviour
-- Suppose orders.product_id has some NULLs:
INSERT INTO orders (user_id, product_id, amount, status, created_at)
VALUES (1, NULL, 50.00, 'pending', NOW());

SELECT
    COUNT(*)                    AS total_rows,       -- counts NULLs too
    COUNT(product_id)           AS non_null_products, -- excludes NULLs
    COUNT(DISTINCT product_id)  AS distinct_products  -- distinct non-NULLs only
FROM orders;
-- total_rows=1001, non_null_products=1000, distinct_products=maybe 200

-- Counting distinct (user_id, product_id) combinations — no native syntax:
-- Option 1: subquery dedup then count
SELECT COUNT(*) AS distinct_combos
FROM (
    SELECT DISTINCT user_id, product_id
    FROM orders
    WHERE product_id IS NOT NULL
) sub;

-- Option 2: hash concatenation (less safe — collision risk for short values)
SELECT COUNT(DISTINCT CONCAT(user_id, '-', product_id)) AS distinct_combos
FROM orders;

HAVING vs WHERE

-- Goal: find departments where active employees' average salary exceeds 70 000

-- WRONG: using HAVING to filter non-aggregated conditions (status check)
SELECT department_id, AVG(salary) AS avg_sal
FROM employees
GROUP BY department_id
HAVING AVG(salary) > 70000
   AND status = 'active';   -- ERROR or logical mistake: status is a row-level filter
-- In PostgreSQL this actually raises an error because status is not in GROUP BY.

-- CORRECT: WHERE eliminates inactive rows first, HAVING filters groups
SELECT department_id, AVG(salary) AS avg_sal
FROM employees
WHERE status = 'active'              -- row-level filter: runs BEFORE GROUP BY
GROUP BY department_id
HAVING AVG(salary) > 70000;         -- group-level filter: runs AFTER GROUP BY

-- The WHERE version is also faster: fewer rows enter the GROUP BY.

COUNT, SUM, AVG, MIN, MAX Deep Dive

-- Demonstrate NULL behaviour with orders (some amounts may be NULL)
SELECT
    COUNT(*)             AS total_rows,           -- includes rows where amount IS NULL
    COUNT(amount)        AS non_null_amounts,      -- skips NULL amounts
    COUNT(DISTINCT user_id) AS distinct_users,     -- distinct non-NULL user_ids
    SUM(amount)          AS total_revenue,         -- NULL rows ignored; result is NULL if ALL are NULL
    AVG(amount)          AS avg_amount,            -- divides by COUNT(amount), not COUNT(*)
    COALESCE(SUM(amount), 0) AS safe_total        -- convert NULL result to 0
FROM orders;

-- Gotcha: average differs when NULLs are present
-- If 10 rows exist, 3 have NULL amount, 7 have values:
-- AVG(amount) = SUM of 7 values / 7    (NOT / 10)
-- This may be correct or incorrect depending on business logic

-- AVG precision: use ROUND + CAST for money
SELECT ROUND(AVG(salary::NUMERIC), 2) AS avg_salary FROM employees;