Aggregate Functions

-- One row per student
SELECT name, marks FROM students;

-- One row, full stop
SELECT AVG(marks) AS class_average FROM students;

-- Several aggregates computed in one pass over the table
SELECT
    COUNT(*)   AS students,
    MIN(marks) AS lowest,
    MAX(marks) AS highest,
    AVG(marks) AS average,
    SUM(marks) AS total_marks
FROM students;

-- This asks for one row and many rows at the same time.
-- Most databases reject it; a loose MySQL returns a misleading answer.
-- SELECT name, AVG(marks) FROM students;

-- All three forms, side by side
SELECT
    COUNT(*)               AS all_rows,
    COUNT(phone)           AS have_a_phone,
    COUNT(DISTINCT branch) AS branches
FROM students;

-- The gap between the first two is exactly the number of NULLs
SELECT COUNT(*) - COUNT(phone) AS missing_phone FROM students;
SELECT COUNT(*) AS missing_phone FROM students WHERE phone IS NULL;

-- What share of students gave a phone number?
-- Multiplying by 100.0 keeps the division out of whole numbers
SELECT ROUND(100.0 * COUNT(phone) / COUNT(*), 1) AS percent_with_phone
FROM students;

-- COUNT(branch) is NOT the number of branches - it is the number of
-- students whose branch is filled in
SELECT COUNT(branch)          AS students_with_a_branch FROM students;
SELECT COUNT(DISTINCT branch) AS how_many_branches      FROM students;

-- AVG divides by the number of non-NULL marks, not by the row count
SELECT
    COUNT(*)                AS students,
    COUNT(marks)            AS marked,
    SUM(marks)              AS total,
    AVG(marks)              AS avg_over_marked,
    AVG(COALESCE(marks, 0)) AS avg_treating_blank_as_zero
FROM students;

-- Nothing to add up returns NULL, not zero
SELECT SUM(total_amount) FROM orders WHERE status = 'refunded';

-- Say what should happen instead
SELECT COALESCE(SUM(total_amount), 0) AS refunded_total
FROM orders
WHERE status = 'refunded';

-- Force decimal arithmetic so the division is never truncated
SELECT SUM(marks) * 1.0 / COUNT(*) AS avg_over_everyone FROM students;

-- Round for display, not for storage
SELECT ROUND(AVG(marks), 2) AS class_average FROM students;

-- Any comparable type works
SELECT
    MIN(marks)     AS lowest_mark,
    MAX(marks)     AS highest_mark,
    MIN(joined_on) AS first_joined,
    MAX(joined_on) AS most_recent,
    MIN(name)      AS first_alphabetically
FROM students;

-- WRONG: name and MAX(marks) are not connected to each other
-- SELECT name, MAX(marks) FROM students;

-- Right, version 1: exactly one row, tie broken by id
SELECT name, marks
FROM students
ORDER BY marks DESC, id ASC
LIMIT 1;

-- Right, version 2: every student who tied for the top mark
SELECT name, marks
FROM students
WHERE marks = (SELECT MAX(marks) FROM students);

-- The same shape answers "the latest order overall"
SELECT id, student_id, ordered_at
FROM orders
ORDER BY ordered_at DESC, id DESC
LIMIT 1;

-- WHERE first, aggregate second
SELECT COUNT(*) AS cse_students, AVG(marks) AS cse_average
FROM students
WHERE branch = 'CSE';

-- Not allowed: the average does not exist while WHERE is running
-- SELECT name FROM students WHERE marks > AVG(marks);

-- Correct: work the average out in a subquery first
SELECT name, marks
FROM students
WHERE marks > (SELECT AVG(marks) FROM students)
ORDER BY marks DESC;

-- Conditional aggregation: one pass, several answers
SELECT
    COUNT(*)                                          AS total,
    SUM(CASE WHEN branch = 'CSE' THEN 1 ELSE 0 END)   AS cse,
    SUM(CASE WHEN branch = 'ECE' THEN 1 ELSE 0 END)   AS ece,
    COUNT(CASE WHEN marks >= 85 THEN 1 END)           AS distinctions,
    ROUND(AVG(CASE WHEN branch = 'CSE' THEN marks END), 1) AS cse_average
FROM students;

-- An aggregate in a subquery is just a value to the outer query
SELECT id, total_amount
FROM orders
WHERE total_amount > (SELECT AVG(total_amount) FROM orders)
ORDER BY total_amount DESC;

-- Always show the count alongside the average
SELECT
    COUNT(*)                    AS orders_counted,
    ROUND(AVG(total_amount), 2) AS avg_order_value,
    MIN(total_amount)           AS smallest,
    MAX(total_amount)           AS largest
FROM orders
WHERE status <> 'cancelled';

-- Collapsing rows into a single string is dialect-specific
-- MySQL
SELECT GROUP_CONCAT(name ORDER BY name SEPARATOR ', ') AS cse_students
FROM students
WHERE branch = 'CSE';

-- PostgreSQL, and SQL Server 2017+
-- SELECT STRING_AGG(name, ', ' ORDER BY name) AS cse_students
-- FROM students WHERE branch = 'CSE';