SQL aggregates: COUNT, SUM, AVG, MIN and MAX

Most questions about data are questions about many rows at once: how many orders came in, how much they were worth, what the average rating was, which order was the biggest. An aggregate function answers them. It reads a whole set of rows and gives back a single value.

The five basic aggregates, count, sum, avg, min and max, exist in every engine this track covers. This lesson uses them on a small table of grocery orders. The examples run in SQLite 3.49.1, the engine of this page’s Run button (sql.js 1.14.2), and the notes say where PostgreSQL and MySQL behave differently.

Five aggregates, one row

Each example creates its own small table first, so you can see every row it works on. The amounts are stored as whole paise, the hundredth part of a rupee, so that adding them up never loses a fraction.

-- Six orders of a small grocery shop. Amounts are whole paise (100 paise = 1 rupee),
-- so totals stay exact; rating is 1 to 5, or NULL when the customer did not rate.
CREATE TABLE orders (
  id           INTEGER PRIMARY KEY,
  customer     TEXT    NOT NULL,
  city         TEXT    NOT NULL,
  amount_paise INTEGER NOT NULL,
  rating       INTEGER
);
INSERT INTO orders VALUES
  (1, 'Asha',   'Pune',   45000, 5),
  (2, 'Vikram', 'Kochi',  12000, NULL),
  (3, 'Asha',   'Pune',   30000, 4),
  (4, 'Meera',  'Jaipur',  8000, 2),
  (5, 'Tenzin', 'Pune',   99000, NULL),
  (6, 'Vikram', 'Kochi',  15000, 4);

-- Five aggregates over the whole table: six rows in, one row out.
SELECT count(*)                            AS orders,
       sum(amount_paise) / 100.0           AS total_rupees,
       round(avg(amount_paise) / 100.0, 2) AS average_rupees,
       min(amount_paise) / 100.0           AS smallest_rupees,
       max(amount_paise) / 100.0           AS largest_rupees
FROM orders;

┌────────┬──────────────┬────────────────┬─────────────────┬────────────────┐
│ orders │ total_rupees │ average_rupees │ smallest_rupees │ largest_rupees │
├────────┼──────────────┼────────────────┼─────────────────┼────────────────┤
│ 6      │ 2090.0       │ 348.33         │ 80.0            │ 990.0          │
└────────┴──────────────┴────────────────┴─────────────────┴────────────────┘

Six rows went in and one row came out. Without a GROUP BY clause (the next lesson), the whole table counts as a single group, so each aggregate in the SELECT list produces one value and the query returns exactly one row (a HAVING clause, two lessons on, can remove it).

Two details in this query are worth copying:

• sum(amount_paise) / 100.0 divides by 100.0, not 100. With a decimal point on one side the division keeps its fraction; you will see below what happens without it.
• round(…, 2) rounds the average to two decimal places for display. Round only the final value you show, never a number you are still going to calculate with.

NULLs are skipped

Two of the six orders were not rated, so rating is NULL in two rows. Aggregates that take a column ignore the NULLs in it:

-- The same six orders; two of them have no rating (NULL).
CREATE TABLE orders (
  id           INTEGER PRIMARY KEY,
  customer     TEXT    NOT NULL,
  city         TEXT    NOT NULL,
  amount_paise INTEGER NOT NULL,
  rating       INTEGER
);
INSERT INTO orders VALUES
  (1, 'Asha',   'Pune',   45000, 5),
  (2, 'Vikram', 'Kochi',  12000, NULL),
  (3, 'Asha',   'Pune',   30000, 4),
  (4, 'Meera',  'Jaipur',  8000, 2),
  (5, 'Tenzin', 'Pune',   99000, NULL),
  (6, 'Vikram', 'Kochi',  15000, 4);

SELECT count(*)                 AS orders,
       count(rating)            AS rated,
       count(DISTINCT customer) AS customers,
       count(DISTINCT city)     AS cities,
       avg(rating)              AS avg_rating,
       avg(coalesce(rating, 0)) AS avg_if_null_were_0
FROM orders;

┌────────┬───────┬───────────┬────────┬────────────┬────────────────────┐
│ orders │ rated │ customers │ cities │ avg_rating │ avg_if_null_were_0 │
├────────┼───────┼───────────┼────────┼────────────┼────────────────────┤
│ 6      │ 4     │ 4         │ 3      │ 3.75       │ 2.5                │
└────────┴───────┴───────────┴────────┴────────────┴────────────────────┘

• count(*) counts rows: all 6, whatever their columns hold.
• count(rating) counts the rows where rating is not NULL: 4.
• count(DISTINCT customer) counts different non-NULL values. Asha and Vikram ordered twice, so there are 4 customers in 6 orders, and the orders come from 3 cities.
• avg(rating) averages the four ratings that exist: (5 + 4 + 2 + 4) / 4 = 3.75. The two unrated orders are left out, not counted as zero.

If your report really should treat a missing rating as 0, say so with coalesce(rating, 0), which replaces each NULL before the average sees it. That answers a different question, and gives a different number: 2.5. Decide which question you are asking before you choose.

When there are no rows

A WHERE clause can leave nothing for the aggregates to read. Here no order comes from Delhi:

CREATE TABLE orders (
  id           INTEGER PRIMARY KEY,
  customer     TEXT    NOT NULL,
  city         TEXT    NOT NULL,
  amount_paise INTEGER NOT NULL,
  rating       INTEGER
);
INSERT INTO orders VALUES
  (1, 'Asha',   'Pune',   45000, 5),
  (2, 'Vikram', 'Kochi',  12000, NULL),
  (3, 'Meera',  'Jaipur',  8000, 2);

-- No order comes from Delhi, so every aggregate below sees zero rows.
-- The query still returns exactly one row.
SELECT count(*)                       AS orders,
       sum(amount_paise)              AS total,
       sum(amount_paise) IS NULL      AS total_is_null,
       coalesce(sum(amount_paise), 0) AS total_or_0,
       total(amount_paise)            AS sqlite_total,
       max(amount_paise)              AS largest
FROM orders
WHERE city = 'Delhi';

┌────────┬───────┬───────────────┬────────────┬──────────────┬─────────┐
│ orders │ total │ total_is_null │ total_or_0 │ sqlite_total │ largest │
├────────┼───────┼───────────────┼────────────┼──────────────┼─────────┤
│ 0      │       │ 1             │ 0          │ 0.0          │         │
└────────┴───────┴───────────────┴────────────┴──────────────┴─────────┘

The query still returns one row, but most of its values are NULL:

• count(*) is 0. Counting nothing gives zero.
• sum, avg, min and max are NULL: there is no total, average, smallest or largest value of nothing. This is what the SQL standard asks for, and PostgreSQL, MySQL and SQLite all do it.
• For a report that should say 0, wrap the sum: coalesce(sum(amount_paise), 0).

SQLite also has total(), which returns 0.0 when there are no rows. It always returns a floating-point number, and PostgreSQL and MySQL do not have it, so coalesce(sum(…), 0) is the portable choice.

A common mistake: an average by hand

avg() exists, but people often compute an average themselves, as a sum divided by a count. With integer columns that goes wrong quietly:

CREATE TABLE ratings (rating INTEGER);
INSERT INTO ratings VALUES (5), (4), (2), (4);

-- 15 / 4 with two integers is integer division: the fraction is cut off.
SELECT sum(rating) / count(rating) AS by_hand,
       avg(rating)                 AS with_avg
FROM ratings;

┌─────────┬──────────┐
│ by_hand │ with_avg │
├─────────┼──────────┤
│ 3       │ 3.75     │
└─────────┴──────────┘

sum(rating) is 15 and count(rating) is 4, and both are integers. In SQLite and in PostgreSQL, dividing an integer by an integer gives an integer, truncated toward zero, so the result is 3 instead of 3.75. MySQL’s / operator returns a decimal instead (3.7500), which is one more reason not to depend on it. Use avg(), or make one side a decimal before dividing: sum(rating) * 1.0 / count(rating).

MIN and MAX work on text and dates too

min and max are not only for numbers. They compare values the way ORDER BY would sort them, so on text they pick the first and last value in sort order:

-- MIN and MAX compare text the way ORDER BY sorts it, character by character.
CREATE TABLE deliveries (
  id         INTEGER PRIMARY KEY,
  rider      TEXT,
  iso_date   TEXT,   -- year-month-day: sorts in date order
  dd_mm_yyyy TEXT    -- day-month-year: sorts by the day first
);
INSERT INTO deliveries VALUES
  (1, 'Lalitha', '2026-01-31', '31-01-2026'),
  (2, 'Farhan',  '2026-03-05', '05-03-2026'),
  (3, 'Joseph',  '2025-12-24', '24-12-2025');

SELECT min(rider)      AS first_rider,
       max(iso_date)   AS latest_iso,
       max(dd_mm_yyyy) AS latest_dd_mm_yyyy
FROM deliveries;

┌─────────────┬────────────┬───────────────────┐
│ first_rider │ latest_iso │ latest_dd_mm_yyyy │
├─────────────┼────────────┼───────────────────┤
│ Farhan      │ 2026-03-05 │ 31-01-2026        │
└─────────────┴────────────┴───────────────────┘

min(rider) is Farhan, the first name alphabetically. The latest delivery is right in iso_date, because a year-month-day date sorts like the date it stands for. In dd_mm_yyyy the same dates sort by their day first, so max picks 31-01-2026 although 05-03-2026 came later. Store dates in ISO form (2026-03-05) in SQLite, or in a real date type in PostgreSQL and MySQL, and min and max will mean earliest and latest.

Text comparison follows the column’s collation, so letter case and accents can change the answer; a later lesson on collations covers that.

Aggregates do not belong in WHERE

To list the orders above the average amount, the obvious query fails:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount_paise INTEGER);
INSERT INTO orders VALUES
  (1, 'Asha', 45000), (2, 'Vikram', 12000), (3, 'Asha', 30000),
  (4, 'Meera', 8000), (5, 'Tenzin', 99000), (6, 'Vikram', 15000);

-- Wrong: WHERE runs row by row, before any average exists.
SELECT customer, amount_paise
FROM orders
WHERE amount_paise > avg(amount_paise);

Parse error near line 7: misuse of aggregate function avg()

WHERE looks at one row at a time and decides which rows reach the aggregates, so it is evaluated before any average exists. SQLite rejects the query with “misuse of aggregate function”, and PostgreSQL and MySQL reject it too. One fix is a subquery: a query in brackets that computes the average first, so WHERE can compare every row with that one number.

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, amount_paise INTEGER);
INSERT INTO orders VALUES
  (1, 'Asha', 45000), (2, 'Vikram', 12000), (3, 'Asha', 30000),
  (4, 'Meera', 8000), (5, 'Tenzin', 99000), (6, 'Vikram', 15000);

-- The subquery computes the average once (34833.33 paise); WHERE then
-- compares each row with that single number.
SELECT customer, amount_paise
FROM orders
WHERE amount_paise > (SELECT avg(amount_paise) FROM orders)
ORDER BY amount_paise DESC;

┌──────────┬──────────────┐
│ customer │ amount_paise │
├──────────┼──────────────┤
│ Tenzin   │ 99000        │
│ Asha     │ 45000        │
└──────────┴──────────────┘

When the condition is about the aggregate of each group, such as “customers with more than two orders”, the tool is HAVING, two lessons on.

How the engines differ

All three engines agree on what the aggregates mean. They differ in the types of the results and in what dividing two integers gives:

Key takeaways

• An aggregate turns many rows into one value. Without GROUP BY (and HAVING), a query with aggregates returns exactly one row.
• count(*) counts rows; count(column) counts non-NULL values; count(DISTINCT column) counts different non-NULL values.
• sum, avg, min and max skip NULLs, and return NULL when there are no rows; count returns 0. Use coalesce(sum(x), 0) when a report needs a zero.
• An integer divided by an integer is truncated in SQLite and PostgreSQL: use avg(), or divide by a decimal such as 100.0.
• min and max follow sort order, so they work on text, and on dates stored in ISO form.
• Aggregates cannot appear in WHERE; compare with a subquery, or filter groups with HAVING.