Himanshu SharmaSoftware engineer
Let’s talk

SQL · Lesson 17 of 49

Summary functions: count, total and average rows

Summarise a whole table with COUNT, SUM, AVG, MIN and MAX, learn how NULL and empty results behave, and avoid the usual aggregate mistakes, with checked output.

15 minSummarising dataUpdated 14 November 2026

Awaiting technical review. This lesson is readable but not yet listed in search engines.

By the end of this lessonSummarise many rows into one answer using COUNT, SUM, AVG, MIN and MAX, and predict how NULL values and empty results affect them.

After this lesson you can answer whole-table questions with one query: how many employees are there, what is the total payroll, who was hired first, what is the average salary? These questions all collapse many rows into a single answer, and SQL does it with five aggregate functions.

All examples run on the sample company database, tested on SQLite.

From many rows to one

A normal function such as UPPER works on one row and gives one value back, so a table of 20 rows yields 20 results. An aggregate function works on a whole set of rows and returns one value for the set. Used without GROUP BY, the set is every row that passes the WHERE filter, and the result is a single row.

sql
SELECT COUNT(*)    AS people,
       SUM(salary) AS total_payroll,
       AVG(salary) AS average_salary,
       MIN(salary) AS lowest,
       MAX(salary) AS highest
FROM employees;
result
+--------+---------------+----------------+--------+---------+
| people | total_payroll | average_salary | lowest | highest |
+--------+---------------+----------------+--------+---------+
|     20 |       1801000 |        90050.0 |  50000 |  180000 |
+--------+---------------+----------------+--------+---------+

Each function has a simple job:

  • COUNT counts rows or values.
  • SUM adds numbers.
  • AVG calculates the mean, which is the sum divided by the count of values.
  • MIN and MAX find the smallest and largest value.

WHERE still comes first. It decides which rows join the set, and the aggregate then summarises only those:

sql
SELECT COUNT(*)    AS engineers,
       SUM(salary) AS engineering_payroll
FROM employees
WHERE department_id = 1;
result
+-----------+---------------------+
| engineers | engineering_payroll |
+-----------+---------------------+
|         6 |              650000 |
+-----------+---------------------+

Three ways to count

COUNT has three forms, and the differences matter:

sql
SELECT COUNT(*)                    AS all_rows,
       COUNT(department_id)        AS with_department,
       COUNT(manager_id)           AS with_manager,
       COUNT(DISTINCT department_id) AS different_departments
FROM employees;
result
+----------+-----------------+--------------+-----------------------+
| all_rows | with_department | with_manager | different_departments |
+----------+-----------------+--------------+-----------------------+
|       20 |              19 |           14 |                     5 |
+----------+-----------------+--------------+-----------------------+

COUNT(*) counts rows, whatever they contain. COUNT(column) counts only the rows where that column is not NULL, which is why 20 employees give 19 with a department and 14 with a manager. COUNT(DISTINCT column) counts the different non-NULL values, so the five departments that have staff count once each. The empty department, Research, does not appear in the employees table at all, so it cannot be counted here.

A common slip is to use COUNT(column) when you mean COUNT(*), which quietly drops the rows where the column is empty.

How NULL is treated

SUM, AVG, MIN, MAX and COUNT(column) all skip NULL values. This is usually what you want, but it can surprise you. The projects table has an end_date that is NULL for projects that are still running:

sql
SELECT COUNT(end_date) AS finished,
       COUNT(*)        AS all_projects
FROM projects;
result
+----------+--------------+
| finished | all_projects |
+----------+--------------+
|        3 |            5 |
+----------+--------------+

Three projects have ended and two are ongoing. AVG also skips NULL, so its denominator is the number of non-NULL values, not the number of rows. The lesson on CAST and COALESCE shows how replacing NULL with zero changes that.

When there are no rows

What do the functions return when the filter matches nothing? There is no department 99:

sql
SELECT COUNT(*)    AS how_many,
       SUM(salary) AS total,
       AVG(salary) AS average,
       MAX(salary) AS highest
FROM employees
WHERE department_id = 99;
result
+----------+-------+---------+---------+
| how_many | total | average | highest |
+----------+-------+---------+---------+
|        0 | NULL  | NULL    | NULL    |
+----------+-------+---------+---------+

COUNT correctly says zero, but SUM, AVG and MAX return NULL, because there is no value to add or compare. If a report must show 0 for an empty total, use COALESCE(SUM(salary), 0).

MIN and MAX on dates and text

MIN and MAX work on anything with an order, not only numbers. On dates they find the earliest and latest, and on text they follow alphabetical order:

sql
SELECT MIN(hire_date) AS first_hire,
       MAX(hire_date) AS latest_hire,
       MIN(last_name) AS first_alphabetically
FROM employees;
result
+------------+-------------+----------------------+
| first_hire | latest_hire | first_alphabetically |
+------------+-------------+----------------------+
| 2016-04-25 | 2024-06-10  | Bose                 |
+------------+-------------+----------------------+

Counting distinct values and summing project hours

The assignments table records hours people spent on projects. A few useful questions about it:

sql
SELECT SUM(hours)                    AS total_hours,
       COUNT(*)                      AS assignment_rows,
       COUNT(DISTINCT employee_id)   AS people_involved,
       COUNT(DISTINCT project_id)    AS projects_involved
FROM assignments;
result
+-------------+-----------------+-----------------+-------------------+
| total_hours | assignment_rows | people_involved | projects_involved |
+-------------+-----------------+-----------------+-------------------+
|        4180 |              19 |              15 |                 5 |
+-------------+-----------------+-----------------+-------------------+

There are 19 assignment rows, but only 15 different people, because some people work on more than one project.

Why aggregates cannot go in WHERE

You may be tempted to write WHERE salary > AVG(salary) to find employees paid more than average. It fails. The reason is order: WHERE filters individual rows before any summarising happens, so the average does not exist yet.

sql
SELECT first_name, salary
FROM employees
WHERE salary > AVG(salary);

SQLite reports "misuse of aggregate function AVG()", and other databases give a similar error. The fix for now is a subquery, which calculates the average first and hands it to the outer query:

sql
SELECT first_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees)
ORDER BY salary DESC
LIMIT 5;
result
+------------+--------+
| first_name | salary |
+------------+--------+
| Meera      | 180000 |
| Vikram     | 150000 |
| Arjun      | 125000 |
| Kavya      | 125000 |
| Lata       | 115000 |
+------------+--------+

The lesson on subqueries covers this in depth, and the lesson on the order a query runs in explains the rule. For filtering on a summary per group, HAVING is the answer, coming shortly.

Aggregates mixed with ordinary columns

Mixing an aggregate with a plain column in the same SELECT is a mistake without GROUP BY. If you want the name of the highest-paid person, writing SELECT first_name, MAX(salary) is ambiguous in standard SQL, because the aggregate gives one row but there are many names. PostgreSQL and SQL Server refuse the query. SQLite allows it and picks a name from the row holding the maximum, which is a SQLite-specific behaviour you should not rely on. The portable form uses a subquery:

sql
SELECT first_name, salary
FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);
result
+------------+--------+
| first_name | salary |
+------------+--------+
| Meera      | 180000 |
+------------+--------+

Differences between databases

  • Average of whole numbers. In SQL Server, AVG of an integer column returns an integer, which cuts the fraction. Cast first, as in AVG(CAST(salary AS DECIMAL(12,2))) or multiply by 1.0. MySQL and PostgreSQL return a decimal. SQLite returns a decimal number too, shown here as 90050.0.
  • Display of results. The same average may appear as 90050, 90050.0 or 90050.0000, depending on the database and the column type.
  • String joining aggregates. Several databases offer ways to join the values of many rows into one text value, but the name differs (GROUP_CONCAT in MySQL and SQLite, STRING_AGG in PostgreSQL and SQL Server 2017 and later). It is not part of the five core functions.

For more on MySQL specifically, see aggregate functions in MySQL.

Common mistakes

  • Using an aggregate in WHERE. Use a subquery, or HAVING with GROUP BY.
  • Selecting a plain column next to an aggregate without grouping. It is an error on most databases.
  • Using COUNT(column) for COUNT(*). The rows where the column is NULL vanish from the count.
  • Forgetting that SUM of nothing is NULL. Wrap it in COALESCE when you need 0.
  • Averaging whole numbers on SQL Server and getting a truncated result.

Exercise

Write one query that reports, for the projects table, the number of projects, the total budget, the average budget rounded to a whole number, the smallest budget, and the earliest start date. Name the columns sensibly. Then write a second query listing the projects that have a budget above the average.

Show one possible answer
sql
SELECT COUNT(*)                 AS projects,
       SUM(budget)              AS total_budget,
       ROUND(AVG(budget))       AS average_budget,
       MIN(budget)              AS smallest_budget,
       MIN(start_date)          AS earliest_start
FROM projects;
result
+----------+--------------+----------------+-----------------+----------------+
| projects | total_budget | average_budget | smallest_budget | earliest_start |
+----------+--------------+----------------+-----------------+----------------+
|        5 |      1670000 |       334000.0 |          120000 | 2024-01-15     |
+----------+--------------+----------------+-----------------+----------------+
sql
SELECT name, budget
FROM projects
WHERE budget > (SELECT AVG(budget) FROM projects)
ORDER BY budget DESC;
result
+-----------------+--------+
|      name       | budget |
+-----------------+--------+
| Mobile App      | 650000 |
| Customer Portal | 400000 |
+-----------------+--------+

Key points

  • Aggregate functions collapse many rows into one value. COUNT, SUM, AVG, MIN and MAX are the five you will use constantly.
  • COUNT(*) counts rows. COUNT(column) counts rows where that column is not NULL. COUNT(DISTINCT column) counts different non-NULL values.
  • SUM, AVG, MIN and MAX ignore NULL values. On an empty set, COUNT gives 0 but the others give NULL.
  • You cannot use an aggregate in WHERE, because WHERE runs before the rows are summarised. Use a subquery now, and HAVING after you meet GROUP BY.

Check yourself

1. The employees table has 20 rows and 19 of them have a department_id. What does COUNT(department_id) return?
2. What does SUM(salary) return when no rows match the WHERE clause?
3. Which query finds employees who earn more than the average salary?
4. Which is true of MIN and MAX?