SQL GROUP BY and aggregations: summarising data correctly

From individual rows to summaries

So far, most of our queries have returned information about individual rows. Consider the following orders table:

A query such as SELECT order_id, customer, quantity * unit_price AS order_amount FROM orders; returns one result row for each order. But many business questions are not about individual orders. We may want to know: how many orders were placed? What was the total order value? The average order value? How many orders did each customer place? These questions require us to summarise multiple rows.

SQL provides aggregate functions for this purpose. The most commonly used are COUNT, SUM, AVG, MIN, and MAX. When combined with GROUP BY, they allow us to calculate summaries for different groups of rows.

COUNT: how many rows are there?

COUNT is used when we want to know how many rows meet a requirement. To count all orders, SELECT COUNT(*) AS order_count FROM orders; returns 6, because there are six rows in the table.

COUNT(*) and COUNT(column) are different

SELECT
    COUNT(*) AS total_orders,
    COUNT(discount_amount) AS orders_with_discount_amount
FROM orders;

COUNT(*) counts every row. But COUNT(discount_amount) counts only rows where discount_amount is not NULL. Our values are 150, NULL, 300, NULL, NULL, 200, so only three rows contain a known discount amount.

COUNT(*)       → counts rows
COUNT(column)  → counts non-NULL values

This distinction becomes important when nullable columns are involved.

Counting distinct values

Sometimes we want to count unique values rather than rows. SELECT COUNT(DISTINCT customer) AS customer_count FROM orders; returns 4: the table contains six orders, but only four different customers (Asha, Ben, Carla, Dev).

SUM: adding values together

SUM calculates the total of a numeric expression. To get the total value of all orders, SELECT SUM(quantity * unit_price) AS total_order_value FROM orders; returns 21150. SQL calculates the amount for each order and then adds the results together: 1500 + 3200 + 3600 + 8500 + 750 + 3600 = 21150.

AVG: calculating the average

AVG calculates the arithmetic mean. SELECT AVG(quantity * unit_price) AS average_order_value FROM orders; returns 3525, conceptually 21150 / 6 = 3525.

MIN and MAX: smallest and largest values

SELECT
    MIN(quantity * unit_price) AS smallest_order,
    MAX(quantity * unit_price) AS largest_order
FROM orders;

These return the values themselves. They do not automatically tell us which orders contain those values; finding the complete row associated with a minimum or maximum is a different problem for later.

Aggregation without GROUP BY

When an aggregate function is used without GROUP BY, SQL summarises all qualifying rows into one group.

SELECT
    COUNT(*) AS order_count,
    SUM(quantity * unit_price) AS total_order_value,
    AVG(quantity * unit_price) AS average_order_value,
    MIN(quantity * unit_price) AS smallest_order,
    MAX(quantity * unit_price) AS largest_order
FROM orders;

Six input rows have become one summary row. But what if we need one summary for each customer? That is where GROUP BY becomes useful.

GROUP BY: one summary per group

Consider this requirement: how many orders has each customer placed? SQL can group rows that have the same customer, then COUNT(*) can count the rows inside each group.

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

The important change is the grain of the result. Before grouping, one row represents one order. After GROUP BY customer, one row represents one customer. This is the central idea behind GROUP BY.

Calculating several metrics per group

Once rows are grouped, we can calculate several aggregate values for each group. For each customer, show the number of orders, total order value, and average order value:

SELECT
    customer,
    COUNT(*) AS order_count,
    SUM(quantity * unit_price) AS total_order_value,
    AVG(quantity * unit_price) AS average_order_value
FROM orders
GROUP BY customer
ORDER BY customer;

SQL creates one group for each customer, then calculates each aggregate independently within that group. For Asha, orders 1001 = 1500 and 1005 = 750 give COUNT = 2, SUM = 2250, AVG = 1125.

Grouping by another column

We can change the question simply by changing the grouping column. “How many orders and how much order value for each status?”

SELECT
    status,
    COUNT(*) AS order_count,
    SUM(quantity * unit_price) AS total_order_value
FROM orders
GROUP BY status
ORDER BY status;

The input data has not changed. Only the required summary has changed. This time the result grain is one row per status.

Grouping by multiple columns

GROUP BY can contain more than one column. “How many orders does each customer have in each status?”

SELECT
    customer,
    status,
    COUNT(*) AS order_count
FROM orders
GROUP BY
    customer,
    status
ORDER BY
    customer,
    status;

Now one result row represents one customer-and-status combination. For Ben, Ben | pending and Ben | paid are separate groups.

Decide the result grain first

Before writing GROUP BY, complete this sentence: one result row should represent __________. GROUP BY customer is one row per customer; GROUP BY status is one row per status; GROUP BY customer, status is one row per customer and status. These are three different result grains.

Why selected columns usually need to be grouped or aggregated

Consider SELECT customer, status, SUM(quantity * unit_price) FROM orders GROUP BY customer;. What should SQL return for Ben’s status? Ben has both pending and paid. There is only one result row for Ben, but two possible status values, and SQL cannot choose one without another rule.

That is why, in SQL, selected expressions in a grouped query generally need to be either part of the grouping key, or reduced to one value using an aggregate function. In SELECT customer, COUNT(*), SUM(...) FROM orders GROUP BY customer;, customer defines each group, and the other selected expressions produce one value for each group.

WHERE filters rows; HAVING filters groups

Suppose the requirement is “show total paid order value for each customer.” The word paid describes which individual orders should participate, so filter those rows first with WHERE:

SELECT
    customer,
    SUM(quantity * unit_price) AS paid_order_value
FROM orders
WHERE status = 'paid'
GROUP BY customer
ORDER BY customer;

Dev does not appear because his order is cancelled. Ben’s pending order is also excluded before the customer groups are summarised. A useful mental sequence is: filter rows, then group rows, then calculate aggregates. This is where WHERE belongs.

Filtering aggregated results with HAVING

Now consider a different requirement: “return customers whose total order value is greater than 5,000.” We cannot know whether a customer qualifies until their orders have been grouped and summed (Asha = 2250, Ben = 6800, Carla = 3600, Dev = 8500). The condition applies to the aggregate result, not to an individual order. This is what HAVING is for.

SELECT
    customer,
    SUM(quantity * unit_price) AS total_order_value
FROM orders
GROUP BY customer
HAVING SUM(quantity * unit_price) > 5000
ORDER BY customer;

WHERE and HAVING solve different problems

WHERE status = 'paid' asks “should this order participate?” HAVING SUM(quantity * unit_price) > 5000 asks “should this completed customer group appear in the result?” You can also use both. “Among paid orders, return customers whose total paid order value exceeds 3,000”:

SELECT
    customer,
    SUM(quantity * unit_price) AS paid_order_value
FROM orders
WHERE status = 'paid'
GROUP BY customer
HAVING SUM(quantity * unit_price) > 3000
ORDER BY customer;

Conceptually: WHERE keeps paid orders, GROUP BY creates one group per customer, SUM calculates each customer’s paid order value, and HAVING keeps groups above 3000.

How to spot grouping problems

Look for these signs when reviewing grouped queries:

When something looks wrong, ask first: what does one result row represent? That question often reveals the problem immediately.

Work through four questions

SELECT
    customer,
    COUNT(*) AS paid_order_count,
    SUM(quantity * unit_price) AS paid_order_value
FROM orders
WHERE status = 'paid'
GROUP BY customer
HAVING SUM(quantity * unit_price) > 3000
ORDER BY customer;

One more prediction

Consider the following orders:

Requirement: among paid orders, return customers whose total paid order value is greater than 3,000. Which query correctly expresses the requirement?

-- Option A
WHERE status = 'paid'
  AND SUM(order_amount) > 3000
GROUP BY customer

-- Option B
GROUP BY customer
HAVING SUM(order_amount) > 3000

-- Option C
WHERE status = 'paid'
GROUP BY customer
HAVING SUM(order_amount) > 3000

-- Option D
GROUP BY customer, status

Lock these in

Summary

Aggregate functions summarise multiple rows. Use COUNT(*) to count rows, SUM(amount) to calculate totals, AVG(amount) to calculate averages, and MIN(amount) / MAX(amount) to find the smallest and largest values. Without GROUP BY, an aggregate query summarises all qualifying rows into one result group. With GROUP BY customer, the result becomes one row per customer; with GROUP BY customer, status, the grain changes to one row per customer and status combination. That distinction is critical. Use WHERE for conditions on individual rows and HAVING for conditions on aggregated groups.

Next, we will learn how to express business rules and create different values based on conditions using CASE WHEN.