SQL · Quick Reference

Lesson 7 — Aggregate Functions, GROUP BY & HAVING Cheat Sheet

SQL

Key Ideas

1Core Aggregate Functions. SQL -- COUNT: number of rows SELECT COUNT (*) AS total_students FROM students; SELECT COUNT (email) AS has_email FROM students; -- skips NULLs -- SUM: add up values SE...

2GROUP BY. SQL -- Count students per grade SELECT grade, COUNT (*) AS num_students FROM students GROUP BY grade; -- Average age per grade SELECT grade, AVG (age) AS avg_age FROM ...

3HAVING — Filtering Groups. SQL -- HAVING filters groups (like WHERE but for aggregates) SELECT grade, COUNT (*) AS num FROM students GROUP BY grade HAVING COUNT (*) >= 3 ; -- only grades with...

4Full Aggregation Pipeline. SQL SELECT grade, COUNT (*) AS total_students, AVG (age) AS avg_age, MAX (age) AS oldest FROM students WHERE email IS NOT NULL -- 1. filter rows first GROUP BY grade -...

5NULL in Aggregates. SQL -- COUNT(*) counts all rows including NULLs -- COUNT(col) skips NULLs SELECT COUNT (*) AS total_rows, COUNT (email) AS rows_with_email, COUNT (*) - COUNT (email) A...

Code Examples

-- COUNT: number of rows
SELECT COUNT(*) AS total_students FROM students;
SELECT COUNT(email) AS has_email FROM students;     -- skips NULLs

-- SUM: add up values
SELECT SUM(price) AS revenue FROM orders;

-- AVG: mean value (ignores NULLs)
SELECT ...

-- Count students per grade
SELECT grade, COUNT(*) AS num_students
FROM students
GROUP BY grade;

-- Average age per grade
SELECT grade, AVG(age) AS avg_age
FROM students
GROUP BY grade
ORDER BY avg_age DESC;

-- Multiple GROUP BY columns
SELECT...

-- HAVING filters groups (like WHERE but for aggregates)
SELECT grade, COUNT(*) AS num
FROM students
GROUP BY grade
HAVING COUNT(*) >= 3;    -- only grades with 3+ students

-- HAVING with AVG
SELECT grade, AVG(age) AS avg_age
FROM students
GROU...