SQLCheat Sheet

SQL Aggregation Cheat Sheet

Quick-reference for GROUP BY, aggregate functions, HAVING, and conditional aggregates. Each section includes copy-ready snippets with inline output comments.

GROUP BY

GROUP BY collapses rows into groups. Every non-aggregated column in SELECT must appear in GROUP BY.

Basic GROUP BY

SELECT category, COUNT(*) AS total
FROM products
GROUP BY category;

Group by multiple columns

SELECT country, city, COUNT(*) AS user_count
FROM users
GROUP BY country, city
ORDER BY user_count DESC;

Aggregate Functions: COUNT, SUM, AVG, MIN, MAX

Aggregate functions compute a single value from a set of rows.

Core aggregates

SELECT
  COUNT(*) AS total_orders,
  SUM(amount) AS revenue,
  AVG(amount) AS avg_order,
  MIN(amount) AS smallest,
  MAX(amount) AS largest
FROM orders;

Aggregates with GROUP BY

SELECT
  category,
  COUNT(*) AS products,
  ROUND(AVG(price), 2) AS avg_price
FROM products
GROUP BY category;

HAVING vs WHERE

WHERE filters rows before grouping. HAVING filters groups after aggregation. If the condition uses an aggregate function, it must go in HAVING.

WHERE then HAVING

-- WHERE: only active products (row filter)
-- HAVING: only categories with avg price > 100 (group filter)
SELECT category, AVG(price) AS avg_price
FROM products
WHERE is_active = true
GROUP BY category
HAVING AVG(price) > 100;

Common mistake: aggregate in WHERE

-- WRONG: can't use aggregate in WHERE
SELECT category, SUM(amount)
FROM orders
WHERE SUM(amount) > 1000   -- ERROR!
GROUP BY category;

-- RIGHT: use HAVING
SELECT category, SUM(amount)
FROM orders
GROUP BY category
HAVING SUM(amount) > 1000;

COUNT(*) vs COUNT(column)

COUNT(*) counts all rows. COUNT(column) ignores NULLs in that column.

NULL handling in COUNT

-- Given: users table has NULLs in email column
SELECT
  COUNT(*) AS total_rows,        -- counts all rows
  COUNT(email) AS with_email,    -- skips NULLs
  COUNT(id) AS with_id           -- id is NOT NULL, same as COUNT(*)
FROM users;

LEFT JOIN trap

-- COUNT(*) counts NULL rows from LEFT JOIN (returns 1)
-- COUNT(o.id) correctly returns 0 for unmatched groups
SELECT c.name, COUNT(o.id) AS order_count
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.name;

COUNT(DISTINCT ...)

Count unique values per group instead of total rows.

Unique buyers per category

SELECT category,
  COUNT(DISTINCT user_id) AS unique_buyers
FROM orders
GROUP BY category;

Combine DISTINCT with a condition

-- Unique users who spent over $100
SELECT COUNT(DISTINCT user_id)
FROM orders
WHERE amount > 100;

NULL Handling in Aggregates

All aggregate functions except COUNT(*) ignore NULL values. Use COALESCE to supply defaults.

SUM and AVG ignore NULLs

-- Given values: 10, NULL, 30
-- SUM = 40 (NULL ignored, not treated as 0)
-- AVG = 20 (40 / 2 rows, not 40 / 3)
SELECT
  SUM(score) AS total,     -- 40
  AVG(score) AS average    -- 20
FROM (VALUES (10), (NULL), (30)) AS t(score);

COALESCE to treat NULL as zero

-- Include NULLs as zero in the average
SELECT AVG(COALESCE(score, 0)) AS avg_with_nulls
FROM students;
-- Now NULL scores count as 0 in both sum and count

Conditional Aggregation with CASE

Use CASE inside aggregate functions to count or sum only rows matching a condition.

Count by status

SELECT
  COUNT(*) AS total,
  SUM(CASE WHEN status = 'completed' THEN 1 ELSE 0 END) AS completed,
  SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled
FROM orders;

FILTER clause (PostgreSQL)

-- Cleaner alternative to CASE
SELECT
  COUNT(*) AS total,
  COUNT(*) FILTER (WHERE status = 'completed') AS completed,
  SUM(amount) FILTER (WHERE status = 'completed') AS completed_revenue
FROM orders;

FILTER is PostgreSQL/standard SQL. MySQL and SQL Server use the CASE pattern.

ROLLUP and CUBE

Generate subtotals and grand totals within GROUP BY. ROLLUP creates hierarchical subtotals; CUBE creates all combinations.

ROLLUP (hierarchical subtotals)

SELECT
  COALESCE(region, 'ALL REGIONS') AS region,
  COALESCE(category, 'ALL CATEGORIES') AS category,
  SUM(amount) AS revenue
FROM orders
GROUP BY ROLLUP(region, category)
ORDER BY region, category;
-- Produces: region+category totals, region totals, grand total

CUBE (all dimension combinations)

SELECT region, category, SUM(amount) AS revenue
FROM orders
GROUP BY CUBE(region, category);
-- Produces: every combination of region and category subtotals

ROLLUP gives N+1 grouping levels. CUBE gives 2^N. Use GROUPING() to distinguish NULLs from subtotal rows.

Date Bucketing with GROUP BY

Group timestamps into day, week, or month buckets with DATE_TRUNC (PostgreSQL) or DATE_FORMAT (MySQL).

Monthly revenue (PostgreSQL)

SELECT
  DATE_TRUNC('month', created_at) AS month,
  SUM(amount) AS revenue
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

Monthly revenue (MySQL)

SELECT
  DATE_FORMAT(created_at, '%Y-%m') AS month,
  SUM(amount) AS revenue
FROM orders
GROUP BY DATE_FORMAT(created_at, '%Y-%m')
ORDER BY month;

Common gotcha: grouping raw timestamps

-- WRONG: each unique second creates its own group
SELECT created_at, COUNT(*) FROM orders GROUP BY created_at;

-- RIGHT: truncate to day
SELECT DATE_TRUNC('day', created_at) AS day, COUNT(*)
FROM orders
GROUP BY DATE_TRUNC('day', created_at);

String Aggregation

Combine multiple values into a single comma-separated string per group.

STRING_AGG (PostgreSQL)

SELECT post_id,
  STRING_AGG(tag, ', ' ORDER BY tag) AS tags
FROM post_tags
GROUP BY post_id;

GROUP_CONCAT (MySQL)

SELECT post_id,
  GROUP_CONCAT(tag ORDER BY tag SEPARATOR ', ') AS tags
FROM post_tags
GROUP BY post_id;

Deduplicate with DISTINCT

SELECT user_id,
  STRING_AGG(DISTINCT role, ', ' ORDER BY role) AS roles
FROM user_roles
GROUP BY user_id;

Learn SQL in Depth

SQL Filtering Practice →SQL Joins Practice →SQL Window Functions Practice →

Warm-up1 / 2

Can you write this from memory?

Count the total number of rows in the orders table.

Start Practicing SQL

Free daily exercises with spaced repetition. No credit card required.

← Back to SQL Syntax Practice