Sign in to save your place.

MODULE 8 OF 12

Aggregations & GROUP BY

Summarize data with COUNT, SUM, AVG, MIN, MAX, GROUP BY, and HAVING.

Aggregate functions

Aggregates compute one value from many rows.

FunctionMeaning
COUNT(*) / COUNT(column)Number of rows (or non-NULL values in column)
SUM(column)Sum of values
AVG(column)Average (ignores NULL)
MIN(column) / MAX(column)Smallest / largest value

GROUP BY

GROUP BY splits rows into groups (e.g. by Country) and applies the aggregate per group. Every column in the SELECT list must either be in GROUP BY or be inside an aggregate.

HAVING

HAVING filters groups after aggregation (WHERE filters rows before aggregation). Example: "Countries with more than 10 customers" → GROUP BY Country HAVING COUNT(*) > 10.

Key Takeaway

Use GROUP BY when you want one row per group. Use HAVING to filter those groups by aggregate values.

Flow

FROM → WHERE (filter rows) → GROUP BY (form groups) → aggregate (compute per group) → HAVING (filter groups) → SELECT/ORDER BY.

Aggregate examples

-- Total row count
SELECT COUNT(*) AS TotalOrders FROM Orders;

-- Sum and average amount per customer
SELECT CustomerId, SUM(Amount) AS Total, AVG(Amount) AS AvgAmount
FROM Orders GROUP BY CustomerId;

-- Only customers with total > 1000
SELECT CustomerId, SUM(Amount) AS Total
FROM Orders GROUP BY CustomerId HAVING SUM(Amount) > 1000;

Practice

Write: (1) COUNT of orders per status. (2) MIN and MAX OrderDate per customer. (3) ProductId with AVG(Quantity) > 5 using GROUP BY and HAVING.

Quiz

1. What is HAVING used for?

Filtering rows before grouping
Filtering groups after aggregation
Sorting the result
Joining tables

Correct! HAVING filters groups after GROUP BY and aggregates.

HAVING filters groups (after GROUP BY); WHERE filters rows before grouping.

2. With GROUP BY Country, what can you SELECT?

Country plus aggregate functions (e.g. COUNT(*), SUM(Amount))
Any column without using an aggregate
Only COUNT(*)
Only Country

Correct! You can SELECT grouping columns and aggregates.

With GROUP BY, SELECT can include the grouped columns and any aggregate (COUNT, SUM, etc.).

October 2026: WHERE before HAVING

WHERE filters rows. GROUP BY builds the buckets. Aggregates run on the buckets. HAVING filters the buckets. Putting SUM(amount) > 100 in WHERE is invalid.

COUNT(*) counts rows in the group. COUNT(column) skips nulls in that column. A dashboard that uses the wrong one will not match a hand count.

SELECT customer_id, SUM(amount) AS total FROM sales GROUP BY customer_id HAVING SUM(amount) > 100;

Try this. Group the practice sales by month. Use HAVING to keep months above a total you choose. Confirm a row with a NULL amount changes COUNT(amount) and not COUNT(*).

Knowledge check

Which clause filters rows before they are grouped?