COUNT, SUM and AVG

Until now every query returned rows. An aggregate function returns one number computed from many rows: how many, how much, the average, the smallest, the biggest. This is where SQL starts to feel like the reporting tool it is. It is also where a quiet mistake gives a number that looks right and is not.

COUNT: how many

count(*) counts rows, whatever is in them.

SELECT count(*) AS customers FROM customers;

 customers
-----------
         8
(1 row)

Put a WHERE in front and it counts only what matches. Aggregates run after WHERE has chosen the rows.

SELECT count(*) AS cancelled FROM orders WHERE status = 'cancelled';

 cancelled
-----------
         1
(1 row)

For a date range, write a start that is included and an end that is not. That way you never lose a row that happens at the very end of the last day.

SELECT count(*) AS september_orders
FROM orders
WHERE order_date >= '2026-09-01' AND order_date < '2026-10-01';

 september_orders
------------------
                6
(1 row)

SUM, AVG, MIN and MAX

SUM adds, AVG averages, MIN and MAX find the extremes.

SELECT sum(quantity) AS units_sold, max(quantity) AS biggest_line FROM order_items;

 units_sold | biggest_line
------------+--------------
         41 |           10
(1 row)

SELECT min(price) AS cheapest, max(price) AS dearest, avg(price) AS average FROM products;

 cheapest | dearest |       average
----------+---------+---------------------
     1.25 |   29.90 | 10.5390000000000000
(1 row)

AVG on a numeric column gives a long decimal. Round it for display with round(avg(price), 2), and remember that rounding is for people reading the result, not for the next calculation.

PostgreSQL does whole-number division when both sides are integers. Other databases differ here: MySQL, for example, returns 3.5 for 7 / 2.

SELECT 7 / 2 AS whole, 7 / 2.0 AS with_decimals;

 whole |   with_decimals
-------+--------------------
     3 | 3.5000000000000000
(1 row)

If you divide two integer columns and want a decimal, make one side numeric first: quantity::numeric / 2, or the standard form CAST(quantity AS numeric) / 2.

Aggregates and NULL

Every aggregate except count(*) skips NULL. That is the part that bites. In the shop, six of the eight customers have a city.

SELECT count(*) AS all_rows, count(city) AS with_city, count(DISTINCT city) AS different_cities
FROM customers;

 all_rows | with_city | different_cities
----------+-----------+------------------
        8 |         6 |                3
(1 row)

count(*) counts rows. count(city) counts rows where city is not NULL. count(DISTINCT city) counts different values: Manila, Cebu and Davao.

The same skipping changes an average. Here are three scores where one is missing.

SELECT avg(score) AS skips_null, avg(COALESCE(score, 0)) AS counts_as_zero
FROM (VALUES (10), (20), (NULL)) AS t(score);

     skips_null      |   counts_as_zero
---------------------+---------------------
 15.0000000000000000 | 10.0000000000000000
(1 row)

15 against 10. Neither is wrong, but they answer different questions: "the average of the scores we have" and "the average if a missing score counts as zero". Decide which one you mean before you write the query, not after you see the number.

An aggregate over no rows is also different from what you might guess. COUNT gives 0. SUM, AVG, MIN and MAX give NULL.

SELECT count(*) AS lines, sum(quantity) AS units, COALESCE(sum(quantity), 0) AS units_or_zero
FROM order_items WHERE order_id = 999;

 lines | units | units_or_zero
-------+-------+---------------
     0 |       |             0
(1 row)

One number, or one number per thing

An aggregate with no GROUP BY collapses the whole result into a single row. You cannot mix it with an ordinary column in the same SELECT, because the database does not know which customer's name to show next to the count.

SELECT name, count(*) FROM customers;

ERROR:  column "customers.name" must appear in the GROUP BY clause or be used in an aggregate function
LINE 1: SELECT name, count(*) FROM customers;
               ^

The next lesson shows how to get one number per group, so that a name and a count can sit side by side.

Common mistakes

• Using count(column) when you mean count(*), and losing every row where the column is NULL.
• Averaging a column with NULLs and not saying which of the two averages you reported.
• Dividing two integers and getting a whole number.
• Using BETWEEN with dates, where the end date's time of day gets cut off.
• Rounding in the middle of a calculation instead of at the end.

If you skip this, your first dashboard number will be off by a few rows or a few percent, and nobody will see why. Which number in your work today is an average that someone has never checked for missing values?