Lesson Description
Reduce many rows to a single value.
Lesson 01
Lesson Description
Reduce many rows to a single value.
Easy Project
Mini project: Produce an executive KPI table with totals and averages per department, sorted by highest business impact.
Every query you have written so far returns one output row per input row (or fewer, once WHERE removes some). That is perfect for listing things, but most business questions are not lists. "How many employees do we have?" "What is our total payroll?" "When is the last appointment on the calendar?" Each of those wants a single number, not forty rows you have to add up yourself.
Aggregate functions do exactly that: they read a whole set of rows and collapse it into one value. In this lesson you will meet the five you will use every day, COUNT, SUM, AVG, MIN and MAX, and learn the NULL rules that trip people up.
SELECT
AGGREGATE_FUNCTION(expression) AS alias,
...
FROM table_name
WHERE optional_row_filter;
| Function | Returns | Works on |
|---|---|---|
COUNT(*) | Number of rows | Any table |
COUNT(expr) | Number of rows where expr is not NULL | Any type |
SUM(expr) | Total of all non-NULL values | Numbers |
AVG(expr) | Mean of all non-NULL values | Numbers |
MIN(expr) / MAX(expr) | Smallest / largest non-NULL value | Numbers, dates, text |
Without a GROUP BY (the next lesson), an aggregate query always returns exactly one row, even if the table is empty.
The HR team wants a headcount, and also wants to know how many people report to a manager.
SELECT
COUNT(*) AS total_employees,
COUNT(manager_id) AS with_manager
FROM employees;
This returns one row: total_employees = 40, with_manager = 39. Why the difference? COUNT(*) counts rows, full stop. COUNT(manager_id) counts only rows where manager_id has a value. One employee, Steven King, the President at the top of the org chart, has a NULL manager_id, so they are skipped. This is the single most important idea in the lesson: pick COUNT(*) when you mean "how many rows" and COUNT(column) when you mean "how many rows have this filled in."
Finance asks for total payroll plus the range and average of salaries.
SELECT
SUM(salary) AS total_payroll,
ROUND(AVG(salary), 2) AS avg_salary,
MIN(salary) AS lowest,
MAX(salary) AS highest
FROM employees;
Result: 333000.00, 8325.00, 2500.00, 24000.00. You can put as many aggregates in one SELECT as you like, and PostgreSQL computes all of them in a single scan of the table.
Notice the ROUND. Without it, AVG(salary) comes back as 8325.0000000000000000. PostgreSQL returns AVG of a numeric (or integer) column as numeric with plenty of decimal places, so wrapping it in ROUND(..., 2) is a habit worth forming for anything a human will read.
In the store sandbox, you want a quick profile of the catalog and of the order history.
SELECT
COUNT(*) AS products,
MIN(price) AS cheapest,
MAX(price) AS priciest,
SUM(stock) AS units_in_stock
FROM products;
This returns 27 products, ranging from 12.50 to 349.00, with 1963 units on the shelves.
MIN and MAX also work on dates and text, because both have a natural sort order. In the hospital sandbox:
SELECT
MIN(date_of_birth) AS oldest_dob,
MAX(date_of_birth) AS youngest_dob,
MIN(last_name) AS first_alphabetically
FROM patients;
The oldest patient was born on 1944-02-27, the youngest on 2018-03-25, and Adams sorts first alphabetically. Be careful with the wording: the minimum birth date belongs to the oldest person.
You saw WHERE in the fundamentals course. It runs before the aggregate, so it decides which rows get fed in. How many appointments are still scheduled, and what date range do they cover?
SELECT
COUNT(*) AS scheduled_count,
MIN(appointment_date) AS next_appointment,
MAX(appointment_date) AS last_appointment
FROM appointments
WHERE status = 'scheduled';
Result: 20 scheduled appointments, from 2025-04-08 to 2025-05-20. Out of 66 appointments in total, only the 20 matching rows were counted.
The argument to an aggregate can be any expression, not just a bare column. For product 11 in the store, how many units have sold and at what average price?
SELECT
SUM(quantity) AS units_sold,
ROUND(AVG(unit_price), 2) AS avg_unit_price,
COUNT(*) AS line_items
FROM order_items
WHERE product_id = 11;
This returns 17 units across 4 order lines at an average of 19.99. You could just as easily write SUM(quantity * unit_price) to total up money, which is the pattern you will need in one of the exercises below.
Two rules explain almost every surprising aggregate result.
Rule 1: every aggregate except COUNT(*) ignores NULLs. SUM adds only the non-NULL values, AVG divides by the number of non-NULL values (not the number of rows), and MIN/MAX skip NULLs entirely. That means AVG(col) is not always equal to SUM(col) / COUNT(*). It equals SUM(col) / COUNT(col).
Rule 2: on zero rows, COUNT returns 0 and everything else returns NULL. The Accounting department (id 110) exists but has no employees:
SELECT
COUNT(*) AS headcount,
SUM(salary) AS payroll,
AVG(salary) AS avg_salary
FROM employees
WHERE department_id = 110;
You get one row: 0, NULL, NULL. That is logically sound (the average of nothing is unknown), but a report showing a blank payroll can confuse people. When you want a zero, say so:
SELECT COALESCE(SUM(salary), 0) AS payroll
FROM employees
WHERE department_id = 110;
Now the result is 0.
In terms of processing order, PostgreSQL reads the FROM table, applies WHERE, and only then feeds the surviving rows into the aggregates. The SELECT list is evaluated last, which is why you can wrap an aggregate in ROUND or COALESCE there.
You want the name of the top earner, so you try:
SELECT first_name, MAX(salary)
FROM employees;
PostgreSQL refuses:
ERROR: column "employees.first_name" must appear in the GROUP BY clause
or be used in an aggregate function
The aggregate collapses 40 rows into one, but first_name has 40 different values. PostgreSQL will not guess which one you meant. To find who earns the most, sort and limit instead: ORDER BY salary DESC LIMIT 1, which you learned in the fundamentals course.
SELECT first_name, last_name
FROM employees
WHERE salary > AVG(salary);
ERROR: aggregate functions are not allowed in WHERE
WHERE judges one row at a time, before any aggregation has happened, so the average does not exist yet. The fix is to compute the average separately in a subquery:
SELECT COUNT(*) AS above_avg
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);
This returns 16. You will study subqueries properly later in the course path; for now, just remember that WHERE cannot see aggregates.
If a column has NULLs, COUNT(column) will be lower than the row count, as you saw with manager_id. That is a feature when you want it and a bug when you do not. If the question is "how many rows," use COUNT(*).
Dashboards that add up SUM results can break when one of them is NULL, because anything plus NULL is NULL. Wrap with COALESCE(..., 0) when a missing total should mean zero.
| Behavior | PostgreSQL | Others |
|---|---|---|
AVG of an integer column | Returns numeric with decimals | SQL Server returns an integer (truncated); cast first with AVG(CAST(col AS decimal(10,2))) |
| Plain column next to an aggregate | Error | MySQL with ONLY_FULL_GROUP_BY disabled silently picks an arbitrary value (the mode is on by default in modern versions) |
| Empty-input results | COUNT is 0, others NULL | Same in MySQL, SQL Server and SQLite |
The exercises below this lesson ask you to count employees, find the average product price, report the highest and lowest salary side by side, and total up store revenue from order_items. Each one wants a single-row answer with a specific column name, so read the prompt carefully and alias your columns to match. For the revenue question, remember that the argument to SUM can be an expression.
GROUP BY you always get exactly one row back.COUNT(*) counts rows; COUNT(column) counts non-NULL values in that column.SUM, AVG, MIN and MAX ignore NULLs, and return NULL when there are no input rows. Use COALESCE if you need a zero.WHERE filters rows before aggregation, so it cannot reference an aggregate.AVG results for display; PostgreSQL returns them with many decimal places.Aggregate functions turn many rows into one number: total revenue, average salary, the earliest order. They are the core of every dashboard metric, and knowing how each one treats NULLs and data types keeps those metrics honest.
Salary summary for the whole company
SELECT COUNT(*) AS employees,
ROUND(AVG(salary), 2) AS avg_salary,
MIN(salary) AS lowest,
MAX(salary) AS highest,
SUM(salary) AS payroll
FROM employees;Without GROUP BY, the aggregates collapse the whole table into a single row.
Revenue from line items
SELECT SUM(quantity * unit_price) AS revenue
FROM order_items;The expression is computed per row first, then summed. This is the correct way to total price times quantity.
SELECT AVG(salary) FROM employees not always the same as SUM(salary) / COUNT(*)?AVG ignores NULL salaries while COUNT(*) counts every row, so with NULLs present the manual version divides by too many rows.
Concept check
3 quick questions. One at a time. Instant score at the end.
Return a single column named total with the total number of employees.
No results yet
Run a query to see results here
From the store sandbox, return the average product price as a column called avg_price.
No results yet
Run a query to see results here
Return max_salary and min_salary for all employees (using MAX and MIN).
No results yet
Run a query to see results here
From the store sandbox, return the total revenue as total_revenue — the sum of quantity * unit_price across all order items.
No results yet
Run a query to see results here