Difficulty: Beginner to Intermediate | Prerequisites: Part 1 (Query Foundations). You need to be comfortable with SELECT, FROM, WHERE, and ORDER BY before tackling aggregates.
Up to this point, queries have returned individual rows. Aggregate functions change that: they take a collection of rows and collapse them into a single summary value. COUNT, SUM, AVG, MIN, and MAX answer questions like "how many?", "how much in total?", and "what is the average?". Combined with GROUP BY and HAVING, aggregates let you break data into categories and filter on the summaries themselves. This is where SQL starts answering real business questions rather than just listing records.
Aggregate functions (COUNT, SUM, AVG, MIN, MAX) reduce multiple rows to a single value. LIMIT and OFFSET cap and paginate results. GROUP BY divides rows into groups so aggregates run per group. HAVING filters those groups (the way WHERE filters individual rows). ROUND tidies up decimal output. NULL values are ignored by all aggregate functions except COUNT(*).
Aggregate function
A function that takes a set of rows and returns a single summary value. Think of it as: many rows in, one number out.
COUNT
Returns the number of rows. COUNT(*) counts all rows including NULLs. COUNT(column) counts only non-NULL values in that column.
SUM
Returns the total of all non-NULL numeric values in a column. Returns NULL (not 0) if no rows match the filter.
AVG
Returns the arithmetic mean of all non-NULL numeric values. NULL rows are excluded from both the numerator and the denominator.
MIN
Returns the smallest value. Works on numbers, text (alphabetical order), and dates (oldest date).
MAX
Returns the largest value. Works on numbers, text (alphabetical order), and dates (most recent date).
ROUND
A function that rounds a numeric value to a specified number of decimal places. Without a second argument, it rounds to the nearest integer.
LIMIT
A clause added to the end of a SELECT statement that caps the number of rows returned. Think of it as "show me only the first N results."
OFFSET
A clause used with LIMIT that skips a specified number of rows before returning results. Together, LIMIT and OFFSET create pagination.
GROUP BY
A clause that divides the result set into groups based on one or more columns. Aggregate functions then calculate per group instead of across the entire table.
HAVING
A clause that filters groups created by GROUP BY, based on aggregate conditions. It is the group-level equivalent of WHERE.
DISTINCT
A keyword that removes duplicate values. When used inside COUNT or SUM, it operates only on unique values.
COALESCE
A function that returns its first non-NULL argument. Commonly used to replace NULL aggregate results with 0: COALESCE(SUM(total), 0).
LIMIT caps the rows returned:
SELECT *
FROM invoice
ORDER BY total DESC
LIMIT 5;
OFFSET skips rows before returning results:
SELECT *
FROM invoice
ORDER BY total DESC
LIMIT 5
OFFSET 5;
This skips the first 5 rows and returns the next 5
Together they create pagination (like pages of search results):
Page 1: LIMIT 5 OFFSET 0
Page 2: LIMIT 5 OFFSET 5
Page 3: LIMIT 5 OFFSET 10
If OFFSET exceeds the available rows, zero rows are returned (no error)
If LIMIT exceeds the available rows, all remaining rows are returned
Find the smallest and largest values:
SELECT MIN(total), MAX(total)
FROM invoice;
Combine with WHERE for filtered extremes:
SELECT MIN(total), MAX(total)
FROM invoice
WHERE billing_country = 'Canada';
Work on text (alphabetical order):
SELECT MIN(country), MAX(country)
FROM customer;
-- Returns: Argentina (MIN), USA (MAX)
Work on dates (oldest = MIN, most recent = MAX):
SELECT MIN(birth_date), MAX(birth_date)
FROM employee;
Count all rows:
SELECT COUNT(*)
FROM customer;
Count non-NULL values in a specific column:
SELECT COUNT(company)
FROM customer;
-- Only counts rows where company is not NULL
Count distinct values:
SELECT COUNT(DISTINCT state)
FROM customer;
Combine with WHERE:
SELECT COUNT(customer_id)
FROM customer
WHERE country = 'USA';
Add up all values in a numeric column:
SELECT SUM(total)
FROM invoice;
Combine with WHERE:
SELECT SUM(total)
FROM invoice
WHERE billing_country = 'USA';
SUM with DISTINCT adds only unique values. If values are 1, 2, 2, 4: SUM returns 9, SUM(DISTINCT ...) returns 7
SUM on a non-numeric column produces an error
If no rows match or all values are NULL, SUM returns NULL (not 0). Use COALESCE to get 0:
SELECT COALESCE(SUM(total), 0)
FROM invoice
WHERE invoice_id < 1;
Calculate the average of non-NULL values:
SELECT AVG(total)
FROM invoice;
Cast to a fixed number of decimal places (PostgreSQL-specific):
SELECT AVG(total)::numeric(10,2)
FROM invoice;
Critical behaviour with NULL vs 0:
Given seven rows with totals: 14, 9, 2, 4, 6, 1, and NULL
AVG = (14 + 9 + 2 + 4 + 6 + 1) / 6 = 6.00 (NULL row excluded from both sum and count)
If that NULL is changed to 0: AVG = (14 + 9 + 2 + 4 + 6 + 1 + 0) / 7 = 5.14
Like SUM, if all values are NULL or no rows match, AVG returns NULL. Use COALESCE for a 0 fallback
Round to the nearest integer (default):
SELECT ROUND(unit_price) FROM track;
0.49 rounds to 0, 0.51 rounds to 1, 0.55 rounds to 1
Round to a specific number of decimal places:
SELECT ROUND(unit_price, 1) FROM track;
-- 0.55 becomes 0.6, 0.91 becomes 0.9
Most useful wrapped around AVG for clean output:
SELECT customer_id, ROUND(AVG(total), 2)
FROM invoice
GROUP BY customer_id;
ROUND exists in most databases, unlike the PostgreSQL-specific ::numeric() cast
Divides rows into groups and applies aggregates per group:
SELECT country, COUNT(*)
FROM customer
GROUP BY country;
Rules:
Every non-aggregate column in SELECT must also appear in GROUP BY
Every column in GROUP BY should appear in SELECT (otherwise the output is unlabelled and useless)
Missing the column from GROUP BY produces an error:
-- ERROR:
SELECT country, COUNT(*) FROM customer;
Multiple GROUP BY columns create sub-groups:
SELECT billing_country, billing_state,
SUM(total), ROUND(AVG(total), 2)
FROM invoice
GROUP BY billing_country, billing_state
ORDER BY billing_country, billing_state;
You can use multiple aggregate functions in the same SELECT
Filters groups after GROUP BY (WHERE filters individual rows before grouping):
SELECT billing_country, SUM(total)
FROM invoice
GROUP BY billing_country
HAVING SUM(total) > 50;
Using WHERE instead of HAVING for an aggregate condition does not work. WHERE sees one row at a time and cannot evaluate SUM across a group
The aggregate in HAVING does not have to match the aggregate in SELECT:
SELECT billing_country, COUNT(total)
FROM invoice
GROUP BY billing_country
HAVING SUM(total) > 50;
-- SELECT shows COUNT, but HAVING filters on SUM
Sort by an aggregate:
SELECT billing_country, COUNT(total)
FROM invoice
GROUP BY billing_country
HAVING SUM(total) > 50
ORDER BY SUM(total);
Combine multiple HAVING conditions with AND/OR:
HAVING COUNT(*) > 5 AND COUNT(fax) > 2
WHERE filters individual rows first; then GROUP BY groups the remaining rows; then HAVING filters groups
Execution order: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY
Columns not in GROUP BY and not part of an aggregate cannot go in HAVING. They belong in WHERE:
-- CORRECT: unit_price filter in WHERE, aggregate filter in HAVING
SELECT invoice_id, SUM(unit_price * quantity)
FROM invoice_line
WHERE unit_price > 1
GROUP BY invoice_id
HAVING SUM(unit_price * quantity) > 1
ORDER BY invoice_id;
Columns that are in GROUP BY can appear in either WHERE or HAVING. Both produce the same result, but WHERE is more efficient because it filters before grouping
Build complex queries step by step:
Start with SELECT * FROM table
Add WHERE conditions to filter individual rows
Add GROUP BY for grouping
Add HAVING for group-level filters
Add ORDER BY for sorting
Every dashboard, report, and analytics tool runs aggregate queries. "Total revenue by region" is a SUM with GROUP BY. "Average order value for customers who have placed more than five orders" combines AVG, GROUP BY, and HAVING. Pagination on websites (showing 10 results per page) is LIMIT and OFFSET. Financial reports round currency values with ROUND. COUNT(DISTINCT ...) answers questions like "how many unique visitors did we have last month?"
Students try to use WHERE to filter on aggregate values (e.g., WHERE SUM(total) > 50). WHERE sees individual rows, not groups. Use HAVING for aggregate conditions.
Students forget that AVG excludes NULL values from both the sum and the count. A table with values 10, NULL, and 20 has AVG = 15 (not 10).
Students assume SUM returns 0 when no rows match. It returns NULL. Use COALESCE to get 0.
Students put columns in SELECT that are not in GROUP BY and not inside an aggregate function. This produces an error in PostgreSQL (though some databases silently return unpredictable results).
⚠️ Know the difference between WHERE (filters rows) and HAVING (filters groups). This is one of the most commonly tested distinctions.
⚠️ Understand that NULL is not the same as 0 for aggregate functions. AVG, SUM, COUNT(column), MIN, and MAX all ignore NULLs.
⚠️ COUNT(*) counts all rows. COUNT(column) counts only non-NULL values. Be able to explain the difference.
⚠️ Know the full clause order: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, OFFSET.
⚠️ Be able to write a query that uses both WHERE and HAVING in the same statement.
True or False: WHERE SUM(total) > 100 is valid SQL.
Fill in the blank: The ___ function returns the number of rows, including those with NULL values, when used with *.
True or False: AVG treats NULL values the same as 0.
Fill in the blank: To skip the first 10 rows and return the next 5, use LIMIT 5 _____ 10.
True or False: Every column in the SELECT clause of a GROUP BY query must either be in the GROUP BY clause or inside an aggregate function.
Answers: 1. False (must use HAVING). 2. COUNT. 3. False (NULLs are excluded entirely). 4. OFFSET. 5. True.
Q: Write a query that returns the number of customers in each country, but only for countries with more than 5 customers.
A: SELECT country, COUNT(*) FROM customer GROUP BY country HAVING COUNT(*) > 5;
Q: What is the difference between COUNT(*) and COUNT(company) on the customer table?
A: COUNT(*) returns 59 (total rows). COUNT(company) returns 10 (only rows where the company column is not NULL).
Q: Write a query that returns the average invoice total, rounded to two decimal places, for each customer.
A: SELECT customer_id, ROUND(AVG(total), 2) FROM invoice GROUP BY customer_id ORDER BY customer_id;
Q: Write a query that returns the top 3 invoices by total amount.
A: SELECT * FROM invoice ORDER BY total DESC LIMIT 3;
Q: Explain what happens when SUM is applied to a result set with no matching rows.
A: SUM returns NULL, not 0. To return 0 instead, wrap it in COALESCE: SELECT COALESCE(SUM(total), 0) FROM invoice WHERE invoice_id < 1;
Q: Write a query that finds invoices from the USA where the customer_id is between 20 and 30, grouped by customer, showing only customers whose maximum single invoice exceeds 15.
A: SELECT customer_id, SUM(total), MAX(total) FROM invoice WHERE billing_country = 'USA' AND customer_id BETWEEN 20 AND 30 GROUP BY customer_id HAVING MAX(total) > 15;
Aggregate functions rely on the filtering skills from Part 1 (WHERE, BETWEEN, IN) to narrow data before grouping. They also depend on table structure from Part 2, since SUM and AVG only work on numeric data types. GROUP BY is the foundation for more advanced topics like subqueries and window functions in later units. Understanding NULL behaviour here is essential for INSERT and UPDATE operations covered in Part 4.
aggregate functions, COUNT, SUM, AVG, MIN, MAX, ROUND, GROUP BY, HAVING, LIMIT, OFFSET, DISTINCT, COALESCE, NULL handling, SQL pagination, filtering groups, WHERE vs HAVING, cascading filters, SQL calculations, numeric aggregation, PostgreSQL casting, result set reduction, RDB 101, Sophia Pathways, relational database fundamentals