SQL aggregation with GROUP BY, COUNT, SUM, and HAVING

Who this is for: learners who can read basic SELECT queries and understand joins. It helps to review SQL SELECT and WHERE, SQL joins, and SQL NULL handling first.

What you will learn: group rows to calculate summaries, use COUNT and SUM, distinguish WHERE from HAVING, and avoid a common counting mistake with LEFT JOIN.

The examples use standard SQL aggregation features available in MySQL and PostgreSQL. They have been checked with SQLite, not tested against every MySQL or PostgreSQL version.

Start with the shared sample data

Run this setup in a test database to reproduce the examples. It creates only the two named tables; do not run it where tables with these names already exist.

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    customer_name VARCHAR(50) NOT NULL
);

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    amount DECIMAL(8, 2) NOT NULL
);

INSERT INTO customers (customer_id, customer_name) VALUES
    (1, 'Asha'),
    (2, 'Ben'),
    (3, 'Chloe');

INSERT INTO orders (order_id, customer_id, amount) VALUES
    (101, 1, 120.00),
    (102, 1, 80.00),
    (103, 3, 200.00);

Group rows and calculate summaries

An aggregate function calculates a value from multiple rows. GROUP BY divides rows into groups; here, each customer gets one group. COUNT counts matching orders and SUM adds their amounts:

SELECT
    c.customer_id,
    c.customer_name,
    COUNT(o.order_id) AS order_count,
    SUM(o.amount) AS total_amount
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.customer_name
ORDER BY c.customer_id;

Ben still appears because the query uses a LEFT JOIN. The unmatched order columns are NULL. COUNT(o.order_id) ignores NULL values and returns zero for Ben, while SUM(o.amount) returns NULL because there are no matching amounts. If a report should display zero instead of NULL, use COALESCE:

COALESCE(SUM(o.amount), 0) AS total_amount

Count a real joined row, not the outer-join placeholder

After a LEFT JOIN, a customer without orders still has one result row: the customer columns plus NULLs for the order columns. So COUNT(*) returns 1 for Ben, but COUNT(o.order_id) returns 0 because the order ID is NULL.

SELECT
    c.customer_name,
    COUNT(*) AS joined_rows,
    COUNT(o.order_id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.customer_name
ORDER BY c.customer_id;

Use WHERE before grouping and HAVING after grouping

WHERE filters input rows before they are grouped. For example, count only orders of at least 100. This filters order rows before the aggregate is calculated:

SELECT
    c.customer_name,
    COUNT(o.order_id) AS qualifying_orders,
    SUM(o.amount) AS qualifying_total
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
WHERE o.amount >= 100
GROUP BY c.customer_id, c.customer_name
ORDER BY c.customer_id;

Because the filter is in WHERE, Ben’s unmatched row is removed as well. If the goal is to keep every customer but only match orders of at least 100, place that condition in the ON clause as shown in the NULL lesson.

HAVING filters groups after aggregate values are calculated. To show customers with at least two orders:

SELECT
    c.customer_name,
    COUNT(o.order_id) AS order_count
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.customer_name
HAVING COUNT(o.order_id) >= 2
ORDER BY c.customer_id;

Every selected column that is not wrapped in an aggregate is listed in GROUP BY. Including both the customer ID and name makes the grouping explicit and avoids merging different customers who happen to share a name.

Try it yourself

Write a query that keeps every customer and shows the number of orders below 100. Asha should have one qualifying order; Ben and Chloe should have zero.

SELECT
    c.customer_name,
    COUNT(o.order_id) AS orders_below_100
FROM customers AS c
LEFT JOIN orders AS o
    ON o.customer_id = c.customer_id
    AND o.amount < 100
GROUP BY c.customer_id, c.customer_name
ORDER BY c.customer_id;

Key idea

GROUP BY creates groups for aggregate calculations. Use WHERE to filter input rows and HAVING to filter completed groups. With a LEFT JOIN, count a non-NULL right-side key to count actual matches.