Counting, Grouping and Subqueries - Database Cheat Sheet

GROUP BY drawn as buckets of real rows, then the five aggregate functions, COUNT(*) against COUNT(column), HAVING, and LEFT JOIN for the zero groups. Then every kind of subquery, and the NULL that empties NOT IN.

On the sheet:

- GROUP BY as buckets: real rows into groups, one row per group

- The five aggregates: COUNT, SUM, AVG, MIN and MAX in one query

- COUNT(*), COUNT(column), COUNT(DISTINCT): what each one counts

- Keep the zero groups: LEFT JOIN and COUNT of a column

- HAVING and WHERE: filter groups or filter rows, and the clause order

- Scalar and IN subqueries: compare with one value, match a list

- NOT IN and the NULL trap: no rows at all, and NOT EXISTS

- Correlated, EXISTS and derived tables: per row, yes or no, and a query in FROM

- Common mistakes: three, each with its measured result

Two A4 pages, printed front and back, sized to print once and keep beside you while you work. Written in short sentences and plain English, for readers whose first language is not English and clearer for everyone else because of it.

It pairs with the Counting & Summarizing (Aggregates), Grouping Data: GROUP BY & HAVING, Questions Inside Questions (Subqueries) and Aggregating & Reporting Data books, so you can learn each idea in full and then revise it here in two pages.

Every query on this sheet was run against a live database before the page was printed, so the result you read is the result you get. Instant PDF download. By M.R. Sterling.