Skip to main content
0%

COUNT, SUM, AVG, MIN, MAX

0/4 solved4 to unlock next
Locked

Lesson 01

COUNT, SUM, AVG, MIN, MAX

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.

Syntax

SELECT
  AGGREGATE_FUNCTION(expression) AS alias,
  ...
FROM table_name
WHERE optional_row_filter;
FunctionReturnsWorks on
COUNT(*)Number of rowsAny table
COUNT(expr)Number of rows where expr is not NULLAny type
SUM(expr)Total of all non-NULL valuesNumbers
AVG(expr)Mean of all non-NULL valuesNumbers
MIN(expr) / MAX(expr)Smallest / largest non-NULL valueNumbers, dates, text

Without a GROUP BY (the next lesson), an aggregate query always returns exactly one row, even if the table is empty.

Worked examples

1. Counting rows, and counting values

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

2. A payroll summary in one pass

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.

3. MIN and MAX are not just for numbers

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.

4. Filtering first with WHERE

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.

5. Aggregating an expression

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.

Under the hood: NULLs and empty sets

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.

Common mistakes

Mixing a plain column with an aggregate

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.

Putting an aggregate in WHERE

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.

Expecting COUNT(column) to count rows

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(*).

Forgetting that empty input gives NULL

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.

How other databases differ

BehaviorPostgreSQLOthers
AVG of an integer columnReturns numeric with decimalsSQL Server returns an integer (truncated); cast first with AVG(CAST(col AS decimal(10,2)))
Plain column next to an aggregateErrorMySQL with ONLY_FULL_GROUP_BY disabled silently picks an arbitrary value (the mode is on by default in modern versions)
Empty-input resultsCOUNT is 0, others NULLSame in MySQL, SQL Server and SQLite

Practice

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.

Key takeaways

  • Aggregate functions collapse many rows into one value; without 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.
  • Round AVG results for display; PostgreSQL returns them with many decimal places.

COUNT, SUM, AVG, MIN, MAX: in depth

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.

Worked examples

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.

Common mistakes

  • Mixing aggregates with plain columns: SELECT first_name, MAX(salary) fails in PostgreSQL because it does not know which first_name to show.
  • Averaging integers and expecting decimals in some databases. PostgreSQL's AVG returns numeric, but SQL Server's AVG of an int column returns an int.
  • Summing a column that contains NULLs is fine, but SUM over zero rows returns NULL, not 0. Use COALESCE(SUM(x), 0) in reports.

Other databases

  • MySQL in permissive modes allows non-aggregated columns next to aggregates and picks an arbitrary value; PostgreSQL and SQL Server reject it.
  • PostgreSQL has extra aggregates such as STRING_AGG, ARRAY_AGG and PERCENTILE_CONT; MySQL's equivalent of STRING_AGG is GROUP_CONCAT.
Check yourself: Why is 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

Quick check: COUNT, SUM, AVG, MIN, MAX

3 quick questions. One at a time. Instant score at the end.

Exercise 1

Total number of employees

Enterprise Workforce AnalyticseasyPostgreSQL

Return a single column named total with the total number of employees.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 2

Average product price

Commerce Operations IntelligenceeasyPostgreSQL

From the store sandbox, return the average product price as a column called avg_price.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 3

Highest and lowest salary

Enterprise Workforce AnalyticseasyPostgreSQL

Return max_salary and min_salary for all employees (using MAX and MIN).

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

Exercise 4

Total revenue from all order items

Commerce Operations IntelligenceeasyPostgreSQL

From the store sandbox, return the total revenue as total_revenue — the sum of quantity * unit_price across all order items.

Autocomplete: start typing table/column namesPaste supported

No results yet

Run a query to see results here

0%

COUNT, SUM, AVG, MIN, MAX

0/4 solved4 to unlock next
Locked