SQL GROUP BY and aggregations: summarising data correctly

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

A query such as this one returns one result row for each order:

SELECT
    order_id,
    customer,
    quantity * unit_price AS order_amount
FROM orders;

But many requirements 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;

This 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;

This 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;

This 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;

This 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.

Consider this requirement:

The orders table stores one row for every order. A customer who places three orders appears in three separate rows. But our question is not about individual orders. We want one row per customer, showing how many orders they placed. GROUP BY makes this possible. It groups rows that share the same value, allowing aggregate functions such as COUNT(*) to calculate a result for each group.

Step 1: Understand how rows are grouped

Suppose our orders table contains six orders from four customers. When we write GROUP BY customer, SQL conceptually places every order into a group based on its customer value.

Notice that orders 1001 and 1005 belong to the same group because both have customer = 'Asha'. The same happens for Ben’s two orders, 1002 and 1006. Carla and Dev each have one order, so each forms a group of one.

The important distinction: GROUP BY does not discard repeated customer rows. Every order still contributes to its group.

Step 2: Count the rows in each group

Once the groups are formed, COUNT(*) counts how many rows belong to each one.

We started with six orders, but now have only four groups because there are four distinct customers. Each group produces one summary row.

Step 3: Write the SQL query

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

Read the query in parts:

• GROUP BY customer forms one group for each distinct customer.
• COUNT(*) counts the orders inside each group.
• SELECT customer identifies which customer each count belongs to.
• ORDER BY customer sorts the final result alphabetically.

What changed?

The most important change is the grain of the result, meaning what one row represents.

Before grouping: One row represents one order. We have six rows.

After grouping: One row represents one customer. We have four rows.

This is the central idea behind GROUP BY: many individual rows become one summary row per group.

Remember this distinction

• WHERE asks: Should this individual row be included?
• GROUP BY asks: Which group does this row belong to?
• An aggregate function such as COUNT(*) then asks: What is the summary value for this group?

Once you understand these three questions, grouped queries become much easier to reason about.

Calculating several metrics per group

Once rows are grouped, we can calculate several aggregate values for each group.

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.

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.

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.

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 this query:

SELECT
    customer,
    status,
    SUM(quantity * unit_price) AS total_order_value
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 the following query, customer defines each group, and the other selected expressions produce one value for each group:

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

Suppose the requirement is:

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:

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:

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.

When a grouped query returns unexpected results, look for these common problems:

• Wrong grouping: If the requirement says “per customer” or “per product,” check whether GROUP BY matches that requirement.
• Too many result rows: If you expected one row per customer but got several, check the grouping columns.
• Extra grouping columns: Adding another column to GROUP BY can split existing groups into smaller ones.
• Missing aggregation: If a selected column has different values within a group, decide whether it needs to be grouped or aggregated.
• Unexpected counts: COUNT(*) counts all rows, while COUNT(column) ignores NULL values.
• Incorrect averages: AVG() ignores NULL values. Check whether excluding them matches the requirement.
• Aggregate conditions in WHERE: Use HAVING to filter groups based on SUM, COUNT, or AVG.
• Row filters in HAVING: Use WHERE when you need to filter individual rows before grouping.

The key question: What should one row in the result represent?

Start with this question whenever an aggregation produces unexpected results. It often helps you identify the mistake.

Consider the following orders:

-- 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

Rules to Remember

1. COUNT(*) vs. COUNT(column)
COUNT(*) counts all rows. COUNT(column) counts only non-NULL values.

2. Counting unique values
COUNT(DISTINCT column) counts distinct non-NULL values, not the total number of rows.

3. Aggregate functions
SUM adds values, AVG calculates the average, and MIN and MAX find the smallest and largest values. These functions ignore NULL.

4. Aggregation without GROUP BY
Without GROUP BY, aggregate functions summarise all qualifying rows into one result row.

5. GROUP BY determines the result
GROUP BY customer returns one row per customer. Adding status creates separate groups for each customer and status combination.

6. WHERE vs. HAVING
WHERE filters individual rows before grouping. HAVING filters groups after aggregation.

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.

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