S038 · Aggregations — GROUP BY, HAVING, Subqueries

SELECT dept, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM   employees
GROUP  BY dept
ORDER  BY avg_salary DESC;

"""agg_lab.py — GROUP BY, HAVING, subqueries, and the classic traps."""
from __future__ import annotations
import sqlite3
 
conn = sqlite3.connect(":memory:")
cur = conn.cursor()
 
cur.executescript("""
CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    dept TEXT NOT NULL,
    salary INTEGER,               -- nullable on purpose
    hired TEXT
);
INSERT INTO employees VALUES
    (1,'Alice','Eng',   120000,'2020-03-01'),
    (2,'Bob',  'Eng',    95000,'2021-06-15'),
    (3,'Carol','HR',     80000,'2019-01-10'),
    (4,'Dan',  'Eng',   140000,'2018-09-20'),
    (5,'Eve',  'Sales', 110000,'2022-02-01'),
    (6,'Frank','Sales',  60000,'2023-11-05'),
    (7,'Grace','HR',       NULL,'2024-05-12');
""")
 
def run(label: str, sql: str) -> None:
    print(f"\n-- {label} --")
    for row in cur.execute(sql):
        print(" ", row)
 
# ---- 1. Basic GROUP BY with multiple aggregates ----
run("Headcount, non-null salary count, and avg per dept", """
    SELECT dept,
           COUNT(*)               AS headcount,
           COUNT(salary)          AS with_salary,
           SUM(salary)            AS total_pay,
           AVG(salary)            AS avg_pay,
           MIN(salary)            AS min_pay,
           MAX(salary)            AS max_pay
    FROM employees
    GROUP BY dept
    ORDER BY avg_pay DESC NULLS LAST;
""")
 
# ---- 2. HAVING — filters groups after aggregation ----
run("Depts with avg salary > 90k", """
    SELECT dept, AVG(salary) AS avg_pay
    FROM employees GROUP BY dept
    HAVING AVG(salary) > 90000;
""")
 
# ---- 3. WHERE vs HAVING — filter before AND after ----
run("Filter rows first, then groups: high earners only, dept with >=2 remaining", """
    SELECT dept, COUNT(*) AS n_high_earners
    FROM employees
    WHERE salary > 70000              -- row filter (WHERE)
    GROUP BY dept
    HAVING COUNT(*) >= 2;             -- group filter (HAVING)
""")
 
# ---- 4. Scalar subquery — above the company average ----
run("Employees earning above company average", """
    SELECT name, salary,
           (SELECT ROUND(AVG(salary), 0) FROM employees) AS company_avg
    FROM employees
    WHERE salary > (SELECT AVG(salary) FROM employees)
    ORDER BY salary DESC;
""")
 
# ---- 5. Correlated subquery — top earner per dept ----
run("Top earner per dept (correlated subquery)", """
    SELECT e.name, e.dept, e.salary
    FROM employees e
    WHERE e.salary = (
        SELECT MAX(salary) FROM employees WHERE dept = e.dept
    )
    ORDER BY e.dept;
""")
 
# ---- 6. IN subquery ----
run("Depts that have anyone earning over 100k", """
    SELECT DISTINCT dept
    FROM employees
    WHERE dept IN (
        SELECT dept FROM employees WHERE salary > 100000
    );
""")
 
# ---- 7. COUNT(DISTINCT) — exact but expensive ----
run("Number of distinct salary values", """
    SELECT COUNT(DISTINCT salary) AS n_distinct_salaries FROM employees;
""")
 
# ---- 8. STRING_AGG / GROUP_CONCAT ----
run("Comma-joined names per dept", """
    SELECT dept, GROUP_CONCAT(name, ', ') AS names
    FROM employees GROUP BY dept ORDER BY dept;
""")
 
# ---- 9. Percentile (median) ----
try:
    run("Median salary per dept (Postgres syntax; may fail in SQLite)", """
        SELECT dept, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median
        FROM employees GROUP BY dept;
    """)
except sqlite3.OperationalError as e:
    print(" SQLite doesn't support PERCENTILE_CONT:", e)
    # Fallback: use AVG of MIN and MAX for a very rough proxy
    run("Rough median substitute (min+max)/2", """
        SELECT dept, (MIN(salary) + MAX(salary)) / 2.0 AS rough_median
        FROM employees WHERE salary IS NOT NULL GROUP BY dept;
    """)
 
# ---- 10. TRAP: SUM over empty set is NULL, not 0 ----
run("SUM of no matching rows → NULL", """
    SELECT SUM(salary) FROM employees WHERE dept = 'Legal';
""")
run("Fix: COALESCE(SUM(x), 0)", """
    SELECT COALESCE(SUM(salary), 0) FROM employees WHERE dept = 'Legal';
""")
 
# ---- 11. TRAP: ‘average of averages’ ----
run("WRONG: average of per-dept averages (not the true company average)", """
    SELECT AVG(avg_pay) AS avg_of_avgs FROM (
        SELECT AVG(salary) AS avg_pay FROM employees GROUP BY dept
    );
""")
run("RIGHT: single-pass overall average", """
    SELECT AVG(salary) AS true_company_avg FROM employees;
""")
 
# ---- 12. Strict-mode violation — will error in Postgres, silent in SQLite ----
run("SQLite lets this pass; Postgres rejects (name not in GROUP BY / aggregate)", """
    SELECT dept, name, AVG(salary) FROM employees GROUP BY dept;
""")
 
conn.close()