Systems · Lesson · ~14 min · 5 tasks
SQL COUNT, SUM and AVG, and the rows they leave out
Someone on Reddit asked why their count of the empty cells in a column came back 0, while a query with WHERE … IS NULL found those cells. This page goes through the aggregates one at a time, and it ends with the count that finds them.
By the end: know exactly how many rows each aggregate used · Nothing to install
In this lesson · 10 parts
They were using Oracle 11g. Here is the same kind of count on a small table of employees, where three people have no salary on file. The WHERE keeps exactly those three rows, and COUNT counts them, or so it seems.
Run it before you read on.
0. The WHERE did keep the three rows, Arjun's, Deepika's and Kavita's. Then COUNT(salary) counted the
salaries in those rows, and there are none. No error, and a number that looks like an answer. By the end of this page you will know
which rows each aggregate used, and you will write the count that finds the three.
The one idea: an aggregate counts what is there
An aggregate answers for many rows with one value: how many, the total, the average, the smallest, the largest. COUNT comes in two forms,
and the difference between them is the whole lesson. COUNT(*) counts rows, every one of them.
COUNT(salary) counts the rows where salary has a value, so it skips the empty cells. The asker found the same
rule in Oracle's documentation, so this is not a quirk of SQLite, the database that runs every example on this page.
So in the query above, the WHERE did its job. The three rows were dropped by the count itself, because of what was inside its brackets.
Picture a stack of forms, one for each person, with a box on each form for the salary. COUNT(*) counts the forms.
COUNT(salary) counts the boxes that are filled in. SUM adds up what is written in the boxes, and AVG divides that sum by the
number of filled boxes, not by the number of forms. Every aggregate on this page works on the boxes, except COUNT(*).
Three counts on one table
Every SQL lesson here queries the same small company. Count it three ways in one query.
15, 12 and 9. There are 15 people, 12 salaries on file and 9 people with a manager on file. One query, three different answers to "how many".
This is also the everyday use of the two forms. Before you trust a column in new data, compare COUNT(*) with
COUNT(column): the difference is how many of its cells are empty.
Make the counts add up
Counts that should add up are a quick check that a query means what you think. When they do not add up, look at the column itself before the query: a field that looks like numbers can turn out to hold text.
On this table the check is one line: the rows, the salaries on file, and the difference.
15, 12 and 3. 12 salaries and 3 empty cells make all 15 rows, so nothing is missing from the picture.
Each value once: COUNT(DISTINCT)
The asker's question was about distinct values, and this is where the empty cells catch people out a second time. First, list each
manager_id once.
7 rows, and one of them is NULL. SELECT DISTINCT keeps the empty cells and shows them as one more value. SQLite, PostgreSQL, Oracle, Informix, DB2 and MS-SQL all do the same here: every NULL comes back in a single row.
Now count them. Put DISTINCT inside the brackets, and the aggregate sees each value once before it counts.
COUNT(DISTINCT manager_id) counts the different values in the column, and an empty cell is not a value.
SELECT DISTINCT shows 7 rows. What does COUNT(DISTINCT manager_id) return?
COUNT(DISTINCT manager_id) counts the different values that are there, and NULL is not one of them, so the 7 rows of SELECT DISTINCT become 6.
9 and 6. Nine people have a manager on file, and six different people manage them. The empty cells are in neither number.
DISTINCT inside a count is also a common fix for a count that comes out too big. A join can repeat a row before the count runs, and then counting, with DISTINCT, an id that names each thing once counts each thing once. Joins have their own lesson further down the path.
Adding up: SUM and AVG
SUM adds up the values that are there, and AVG divides that total by how many values there are. Both skip the empty cells.
1,217,000 in total, and an average of about 101,416.67, which is that total divided by 12, the salaries on file. The three empty salaries are in neither the total nor the count it was divided by.
The average has decimals although every salary is a whole number. AVG gives a decimal number whenever it has at least one value, even when every value is a whole number.
Whether 12 is the right number to divide by depends on the question. If an empty salary should count as 0, the average shared across everyone is lower, and that is the answer some people expect from AVG. So whenever you calculate with a column that has empty cells, decide what they mean before you run the numbers. On this table there are two honest averages, and they answer different questions. Run both.
About 101,416.67 and 81,133.33. The first is the average salary of the 12 people who have one on file. The second shares the same total across all 15 people, and so does the third, which counts each empty salary as 0. Neither is wrong, but they are not the same number, so say which one you mean.
The smallest and the largest: MIN and MAX
MAX gives the value that would come last if you sorted the column with ORDER BY, and MIN gives the smallest value that is there. Both skip the empty cells.
78000 and 134000, Anita's and Vikram's, then 2017-05-18 and 2023-08-10, Suresh's and Kavita's hire dates. MIN and MAX work on dates written year first as well as on numbers.
ORDER BY salary puts the empty salaries first. What does MIN(salary) return?
MIN gives the smallest value that is there, so the empty salaries are skipped, although ORDER BY puts them first.
A group with nobody in it
Department 7, Legal, has nobody in it yet. Ask the aggregates about it.
0, then three NULLs. COUNT says nobody. SUM, AVG and MAX have no values to work on, so they give NULL. A total of NULL is not 0.
A SUM of no values is NULL, not 0. SQLite does it this way because the SQL standard requires it and most other databases do the same. Its TOTAL function is the exception, and gives 0.
What does COUNT(*) return for department 7, where nobody works?
COUNT(*) counts the rows in the group, and here there are none, so it says 0. SUM is the one that gives NULL.
The usual fix is COALESCE, which gives back the first value in its list that is not NULL, so COALESCE(SUM(salary), 0) turns
an empty total into 0. IFNULL does the same job with exactly two values.
0 and 0. COALESCE turned the NULL into 0, and TOTAL gave 0 without being asked.
With GROUP BY, a group that has no rows does not appear at all, so there is no row to put the 0 in. GROUP BY is the next lesson.
Back to the question: count the rows, not the values
The asker wanted to know how many rows had an empty field. That is a count of rows, so it is COUNT(*).
3: Arjun, Deepika and Kavita, the rows the WHERE kept.
Your turn
There are five tasks below, all on the same table. Most answers are a single number. Write the query, run it, and the page tells you how it is wrong: the wrong number of rows or columns, or one row with the wrong value in it.
employees: emp_id · first_name · last_name · email · hire_date · dept_id · manager_id · salary
All five. You counted values and rows, added up and averaged what was there, counted each value once, and turned an empty total into 0.
Test yourself
A table has 15 rows, and 3 of them have NULL in salary. What do COUNT(*) and
COUNT(salary) return?
COUNT(*) counts every row, and COUNT(column) counts only the rows where that column is not NULL.
A WHERE matches no rows at all. What does SUM(salary) return?
A SUM over no values is NULL; COALESCE(SUM(...), 0) or TOTAL() gives 0.
Done. This lesson is ticked off on the path, and the questions you answered are now in your review deck.
table
A named set of rows that all have the same columns; CREATE TABLE "is used to create a new table in an SQLite database" and names "each column in the table".
Source: sqlite.org ↗ · checked 13 Oct 2026
WHERE
The clause that keeps only the rows whose test comes out true; a row whose test is false, or unknown because a cell is empty, is dropped without a message.
Source: sqlite.org ↗ · checked 13 Oct 2026
row
One record of a table or of a result; "The result of a SELECT is zero or more rows of data where each row has a fixed number of columns."
Source: sqlite.org ↗ · checked 13 Oct 2026
query
A question put to the database in SQL; a SELECT is the kind that only reads, since "A SELECT statement does not make any changes to the database."
Source: sqlite.org ↗ · checked 13 Oct 2026
column
One named field that every row of a table has; a column definition "consists of the name of the column, optionally followed by the declared type of the column".
Source: sqlite.org ↗ · checked 13 Oct 2026
NULL
A missing value, not zero and not an empty string; in comparisons it "is considered less than any other value (including another value with storage class NULL)".
Source: sqlite.org ↗ · checked 13 Oct 2026
DISTINCT
A word after SELECT that removes duplicate rows from the result: "duplicate rows are removed from the set of result rows".
Source: sqlite.org ↗ · checked 13 Oct 2026
ORDER BY
The clause that decides the order of the rows; without it "the order in which the rows are returned is undefined". ASC is the default and NULLs come first in it.
Source: sqlite.org ↗ · checked 13 Oct 2026
GROUP BY
The clause that deals rows into groups, one group per distinct set of values of the listed columns or expressions, so that each aggregate answers once per group; "For the purposes of grouping rows, NULL values are considered equal."
Source: sqlite.org ↗ · checked 13 Oct 2026
clause
One part of a query that does one job; a simple SELECT runs "FROM clause processing", then "WHERE clause processing", then the result columns, then ORDER BY and LIMIT.
Source: sqlite.org ↗ · checked 13 Oct 2026
comparison
A test between two values with =, !=, <, > and the like; it answers true or false, and NULL when either side is empty.
Source: sqlite.org ↗ · checked 13 Oct 2026
group
The set of rows one aggregate answers for; without GROUP BY the whole result is one group, and "The count(*) function (with no arguments) returns the total number of rows in the group."
Source: sqlite.org ↗ · checked 13 Oct 2026
aggregate
A function that answers for many rows with one value, like COUNT, SUM, AVG, MIN or MAX; every one of them except COUNT(*) skips empty cells.
Source: sqlite.org ↗ · checked 13 Oct 2026
FROM
The clause that names the table or tables the query reads; "the data on which a simple SELECT query operates comes from the one or more tables or subqueries … specified following the FROM keyword".
Source: sqlite.org ↗ · checked 13 Oct 2026
LIMIT
The clause that keeps only the first N rows of the sorted result; when there are fewer than N rows, all of them come back.
Source: sqlite.org ↗ · checked 13 Oct 2026
COUNT
How many; COUNT(*) counts rows, while COUNT(column) "returns a count of the number of times that X is not NULL in a group".
Source: sqlite.org ↗ · checked 13 Oct 2026
SUM
The total of the values that are there; "If there are no non-NULL input rows then sum() returns NULL", because "the SQL standard requires it".
Source: sqlite.org ↗ · checked 13 Oct 2026
AVG
"the average value of all non-NULL X within a group", so an empty cell is left out of both the total and the count it divides by.
Source: sqlite.org ↗ · checked 13 Oct 2026