What is the difference between WHERE and HAVING?

WHERE filters rows before aggregation. HAVING filters groups after aggregation. You cannot use aggregate functions like SUM in a WHERE clause — use HAVING instead.

Why must SELECT columns appear in GROUP BY?

Aggregate functions collapse multiple rows into one. Any non-aggregated column must be in the GROUP BY so the database knows which value to use when multiple source rows map to one output row.

What does COUNT(DISTINCT) do?

COUNT(DISTINCT column) counts unique values in a column within each group, ignoring duplicates. COUNT(*) counts all rows including duplicates.

Format a SQL GROUP BY with Aggregates

Aggregate queries with GROUP BY and HAVING are the backbone of reporting and analytics SQL. This example calculates order statistics per customer with filtering on minimum spend. Proper formatting makes the aggregation logic clear and helps reviewers verify that all non-aggregated SELECT columns appear in the GROUP BY clause. Use this pattern for dashboards, cohort analysis, and business intelligence queries.

Example

SELECT
  c.country,
  DATE_FORMAT(o.created_at, '%Y-%m') AS month,
  COUNT(DISTINCT o.id) AS order_count,
  SUM(o.total) AS revenue,
  AVG(o.total) AS avg_order_value
FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE o.status = 'completed'
  AND o.created_at >= '2024-01-01'
GROUP BY c.country, month
HAVING revenue > 1000
ORDER BY revenue DESC;

[ open in SQL Formatter → ]

FAQ

What is the difference between WHERE and HAVING?: WHERE filters rows before aggregation. HAVING filters groups after aggregation. You cannot use aggregate functions like SUM in a WHERE clause — use HAVING instead.
Why must SELECT columns appear in GROUP BY?: Aggregate functions collapse multiple rows into one. Any non-aggregated column must be in the GROUP BY so the database knows which value to use when multiple source rows map to one output row.
What does COUNT(DISTINCT) do?: COUNT(DISTINCT column) counts unique values in a column within each group, ignoring duplicates. COUNT(*) counts all rows including duplicates.

Related Examples

Format a SQL SELECT with JOIN

Multi-table JOIN queries quickly become hard to read when written on a single li...

Format a SQL UPDATE with WHERE

UPDATE statements with multiple columns and compound WHERE conditions are easy t...

Format a SQL CREATE TABLE Statement

CREATE TABLE statements define the schema of your database and should be readabl...