SQL GROUP BY, Aggregate Functions, and HAVING Explained

GROUP BY changes the grain of a result. Detail rows become one row per distinct grouping key, and aggregates describe the rows that landed in each group. WHERE decides which rows may enter; HAVING decides which completed groups remain.

The safest way to build a grouped query is to write the intended output grain first. Then choose aggregates with explicit rules for NULLs, duplicate rows, and missing groups.

GROUP BY changes the grain

The source is one row per order. Grouping by customer and region produces one row per customer-region combination. Every selected column is either part of that key or an aggregate over the group's rows.

Input: orders

SELECT
  customer_id,
  region,
  COUNT(*) AS order_count
FROM orders
GROUP BY customer_id, region
ORDER BY customer_id, region;

Output

After grouping, a bare detail column such as order_id no longer names one value for the group. Either add a column to the grouping key, which changes the grain, or aggregate it with a rule that states what should survive.

Aggregates have explicit NULL behavior

COUNT(*) counts rows. COUNT(amount) and the numeric aggregates use only non-NULL amounts. The US group therefore has five orders but four known amounts; its average divides the known total by four, not five.

Input: orders

SELECT
  region,
  COUNT(*) AS row_count,
  COUNT(amount) AS known_amounts,
  SUM(amount) AS total_amount,
  ROUND(AVG(amount), 2) AS avg_known_amount,
  MIN(amount) AS min_amount,
  MAX(amount) AS max_amount
FROM orders
GROUP BY region
ORDER BY region;

Output

An all-NULL group returns NULL from SUM, AVG, MIN, and MAX. Use COALESCE only when the reporting contract says that absence should display as zero. The SQL COUNT guide develops the row, non-NULL, and distinct counting choices.

WHERE filters rows; HAVING filters groups

This query first keeps paid order rows. It then forms customer groups and keeps groups whose paid revenue exceeds 300. The clauses are not interchangeable because they act on different grains.

Input: orders

SELECT
  customer_id,
  COUNT(*) AS paid_orders,
  SUM(amount) AS paid_revenue
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING SUM(amount) > 300
ORDER BY customer_id;

Output

WHERE cannot reference SUM(amount) because the aggregate does not exist yet. HAVING can. The full clause sequence is explained in SQL order of operations.

Multiple keys define combinations and duplicates

To find repeated customer-day combinations, group by the columns that define the candidate duplicate and keep groups with more than one row. Do not include the unique order ID, or every group would have size one.

Input: orders

SELECT
  customer_id,
  order_date,
  COUNT(*) AS orders_on_day
FROM orders
GROUP BY customer_id, order_date
HAVING COUNT(*) > 1
ORDER BY customer_id, order_date;

Output

GROUP BY emits only combinations present in the data. A complete region-by-date grid, including zero rows, needs a scaffold of desired keys followed by a left join.

Conditional and distinct aggregates

Conditional aggregates summarize one group several ways. Here every region reports its total rows, paid rows, paid revenue, and paid share. Each numerator and denominator is visible in the same output.

Input: orders

SELECT
  region,
  COUNT(*) AS total_orders,
  COUNT(*) FILTER (WHERE status = 'paid') AS paid_orders,
  COALESCE(SUM(amount) FILTER (WHERE status = 'paid'), 0) AS paid_revenue,
  ROUND(
    COUNT(*) FILTER (WHERE status = 'paid')::numeric / COUNT(*),
    2
  ) AS paid_rate
FROM orders
GROUP BY region
ORDER BY region;

Output

The CASE equivalent is covered in SQL CASE WHEN. With COUNT(CASE ...), omit ELSE 0 because zero is non-NULL and would count.

Distinct aggregation is useful when membership requires separate values rather than a row count. Customer 101 has two orders on March 1; customer 102 has one order on March 1 and one on March 4. Only customer 102 appears on both requested dates.

Input: orders

SELECT customer_id
FROM orders
WHERE order_date IN (DATE '2026-03-01', DATE '2026-03-04')
GROUP BY customer_id
HAVING COUNT(DISTINCT order_date) = 2
ORDER BY customer_id;

Output

For more exercises built around grouping grain and edge fixtures, use SQL practice questions.

FAQ

What columns may appear in SELECT after GROUP BY?

Grouping keys and aggregate expressions are the portable choices. Some engines recognize functional dependencies in specific cases, but listing the intended keys keeps the output grain clear.

Does GROUP BY remove NULL keys?

No. Rows with NULL in a grouping column form a NULL group. Aggregates inside that group still follow their own NULL rules.

When should I use DISTINCT instead of GROUP BY?

Use DISTINCT when you only need unique projected rows. Use GROUP BY when each key needs an aggregate or HAVING condition.

Can HAVING appear without GROUP BY?

PostgreSQL permits it. The input is treated as one group, and HAVING either keeps or removes that aggregate result. Use the form only when a one-group output matches the question.

Why can a grouped query return no rows?

HAVING can remove every group. Downstream code expecting a scalar should distinguish an empty grouped result from an ungrouped aggregate that returns one row containing zero or NULL.