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

By Manish Singh · Published

Fifteen salary chips, three of them NULL, beside the query SELECT COUNT(*), COUNT(salary) FROM employees; and its answers: COUNT(*) is 15, and COUNT(salary) is 12, leaving out the 3 NULLs.
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.

first run fetches SQLite, about 700 KB

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

    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.

    Sources

    The docs

    The people

    • u/spacebandido on r/SQL, 11 Aug 2014: the question this page opens with and the Oracle documentation they found; u/NotImplemented's answer that the asterisk, not the WHERE, decides whether empty cells are counted ↗
    • TheCodingCo on YouTube: COUNT(*) against COUNT(column) as a check on missing data (three and a half minutes) ↗
    • u/Skewjo on r/SQL, 13 Dec 2024: counts that did not add up, and u/throwaway_1234432167's question that found the field held text ↗
    • u/Mission-Example-194 on r/SQL, 21 Apr 2026: a count doubled by a join; u/GrEeCe_MnKy's fix, DISTINCT on the sale id ↗
    • Ankit Srivastava, Daily life of a Data Engineer, on YouTube: an average that leaves out the empty row, and handling NULLs before any calculation (four and a half minutes) ↗
    • Richard Knop on Stack Overflow, 23 Aug 2009: a SUM that came back NULL; Jimmy Stenke's answer with COALESCE ↗
    • Albert Laure on Stack Overflow, 7 Oct 2013: counts that came back blank; Dan's answer that an empty group gets no row under GROUP BY ↗

    Queries run locally in your browser using SQLite compiled to WebAssembly.

    Next in SQL

    GROUP BY & HAVING · 16 min