GROUP BY & HAVING

-- One row per branch
SELECT branch, COUNT(*) AS students
FROM students
GROUP BY branch;

-- Aggregates behave inside each group exactly as they did over the table
SELECT
    branch,
    COUNT(*)             AS students,
    COUNT(marks)         AS marked,
    ROUND(AVG(marks), 1) AS average,
    MIN(marks)           AS lowest,
    MAX(marks)           AS highest
FROM students
GROUP BY branch;

-- Per student rather than per branch, over a different table
SELECT student_id, COUNT(*) AS orders, SUM(total_amount) AS spent
FROM orders
GROUP BY student_id;

-- Rejected, and rightly: which of the twelve CSE names should print?
-- SELECT branch, name, COUNT(*) FROM students GROUP BY branch;

-- Option 1: make the groups finer
SELECT branch, name, COUNT(*) AS rows_for_this_person
FROM students
GROUP BY branch, name;

-- Option 2: reduce the column with an aggregate
SELECT branch, MAX(marks) AS top_mark, COUNT(*) AS students
FROM students
GROUP BY branch;

-- Option 3: group by the primary key, which makes the rest unambiguous
-- (accepted by MySQL 5.7+ and PostgreSQL 9.1+)
SELECT s.id, s.name, COUNT(o.id) AS orders
FROM students s
LEFT JOIN orders o ON o.student_id = s.id
GROUP BY s.id;

-- The portable spelling lists every non-aggregated column
SELECT s.id, s.name, COUNT(o.id) AS orders
FROM students s
LEFT JOIN orders o ON o.student_id = s.id
GROUP BY s.id, s.name;

-- Finer buckets: one row per combination
SELECT branch, is_active, COUNT(*) AS students
FROM students
GROUP BY branch, is_active
ORDER BY branch, is_active;

-- A monthly revenue report (MySQL)
SELECT
    DATE_FORMAT(ordered_at, '%Y-%m') AS month,
    COUNT(*)                         AS orders,
    ROUND(SUM(total_amount), 2)      AS revenue
FROM orders
WHERE status <> 'cancelled'
GROUP BY DATE_FORMAT(ordered_at, '%Y-%m')
ORDER BY month;

-- The same idea with separate year and month columns (MySQL)
SELECT YEAR(ordered_at) AS y, MONTH(ordered_at) AS m, COUNT(*) AS orders
FROM orders
GROUP BY YEAR(ordered_at), MONTH(ordered_at)
ORDER BY y, m;

-- PostgreSQL says the same thing differently
-- SELECT DATE_TRUNC('month', ordered_at) AS month, COUNT(*) AS orders
-- FROM orders
-- GROUP BY DATE_TRUNC('month', ordered_at)
-- ORDER BY month;

-- Months with no orders are missing entirely, not shown as zero
SELECT DATE_FORMAT(ordered_at, '%Y-%m') AS month, COUNT(*) AS orders
FROM orders
GROUP BY DATE_FORMAT(ordered_at, '%Y-%m')
ORDER BY month;

-- Every NULL branch collects into one group
SELECT branch, COUNT(*) AS students
FROM students
GROUP BY branch;
-- one of the returned rows may have branch = NULL

-- Label that group so nobody has to guess what the blank means
SELECT COALESCE(branch, 'not recorded') AS branch, COUNT(*) AS students
FROM students
GROUP BY COALESCE(branch, 'not recorded');

-- WHERE drops whole groups: a branch where everyone failed vanishes
SELECT branch, COUNT(*) AS passed
FROM students
WHERE marks >= 40
GROUP BY branch;

-- Keep every branch, and count the passes inside the aggregate instead
SELECT
    branch,
    COUNT(*)                                AS students,
    COUNT(CASE WHEN marks >= 40 THEN 1 END) AS passed
FROM students
GROUP BY branch;

-- Branches with at least ten students
SELECT branch, COUNT(*) AS students
FROM students
GROUP BY branch
HAVING COUNT(*) >= 10;

-- Both clauses, each doing its own job
SELECT branch, COUNT(*) AS passed, ROUND(AVG(marks), 1) AS avg_of_passers
FROM students
WHERE marks >= 40             -- which students count
GROUP BY branch
HAVING COUNT(*) >= 5          -- which branches are worth showing
ORDER BY avg_of_passers DESC;

-- Students with more than three orders and over 5000 spent in total
SELECT student_id, COUNT(*) AS orders, SUM(total_amount) AS spent
FROM orders
WHERE status <> 'cancelled'
GROUP BY student_id
HAVING COUNT(*) > 3 AND SUM(total_amount) > 5000
ORDER BY spent DESC;

-- Legal but wasteful: builds every group, then discards most of them
-- SELECT branch, COUNT(*) FROM students
-- GROUP BY branch HAVING branch = 'CSE';

-- Do the same filtering here instead
SELECT branch, COUNT(*) AS students
FROM students
WHERE branch = 'CSE'
GROUP BY branch;

-- FROM -> WHERE -> GROUP BY -> HAVING -> SELECT -> ORDER BY -> LIMIT
SELECT branch, COUNT(*) AS students, ROUND(AVG(marks), 1) AS average
FROM students             -- 1. the rows come from here
WHERE is_active = 1       -- 2. rows are filtered
GROUP BY branch           -- 3. rows are bucketed
HAVING COUNT(*) >= 5      -- 4. small buckets are dropped
                          -- 5. SELECT builds the output row
ORDER BY average DESC     -- 6. the alias works, because SELECT ran first
LIMIT 3;                  -- 7. three groups, not three students

-- Top three branches by student count
SELECT branch, COUNT(*) AS students
FROM students
GROUP BY branch
ORDER BY COUNT(*) DESC
LIMIT 3;

-- The five students who have spent the most
SELECT student_id, SUM(total_amount) AS spent
FROM orders
WHERE status <> 'cancelled'
GROUP BY student_id
ORDER BY spent DESC
LIMIT 5;