Aggregate Functions: COUNT, SUM, AVG, MIN, MAX

So far every query returned one result row for each matching row in the table. Business questions are often different. How many orders do we have? What is the total quantity sold? What is the average price? These questions squeeze many rows into one answer. SQL does this with aggregate functions.

COUNT: how many rows?

SELECT COUNT(*) AS customers FROM customers;

customers
8

COUNT(*) counts rows. You can add a WHERE to count only some of them. The WHERE runs first, and the count is done on the rows that remain.

SELECT COUNT(*) AS delivered_orders
FROM orders
WHERE status = 'delivered';

delivered_orders
6

SUM, AVG, MIN and MAX

These four work on a number column.

SELECT
  COUNT(*)   AS products,
  SUM(price) AS total,
  AVG(price) AS average,
  MIN(price) AS cheapest,
  MAX(price) AS dearest
FROM products;

products | total | average | cheapest | dearest
8        | 5110  | 638.75  | 60       | 1800

• SUM(x) adds up all the values.
• AVG(x) is the sum divided by the number of values. Use ROUND(AVG(x), 2) for a tidy number.
• MIN(x) and MAX(x) give the smallest and largest. They also work on text (alphabetical order) and dates (earliest and latest).

Because there is a single row of output, all five answers appear together. You can mix several aggregates in one query, as above. What you cannot do yet is mix an aggregate with a normal column, such as SELECT name, MAX(price). The database would not know which name to show with the average. The next lesson shows how GROUP BY solves this.

How NULL behaves

Aggregate functions skip NULL values. This surprises many people and is a favourite interview question. Look at the customers table, where Hari has no 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        | 7         | 6

COUNT(*) counts every row, so it is 8. COUNT(city) counts only the rows where city is not NULL, so it is 7. COUNT(DISTINCT city) counts different values: Nairobi appears twice, so seven cities give six different ones. The rule: COUNT(*) counts rows, and COUNT(column) counts non-NULL values in that column.

The same skipping applies to AVG, SUM, MIN and MAX. The average of three values 10, 20 and NULL is 15, not 10, because the NULL is ignored and the divide is by 2. If you want NULL treated as zero, say so with AVG(COALESCE(x, 0)).

Calculations inside an aggregate

You can put a calculation inside the brackets. To find the total number of items ever ordered, add the quantity column. To find money, multiply price by quantity for each row first. The money needs the products table, which you will join in lesson 9. For now, one table at a time:

SELECT SUM(quantity) AS items_sold FROM order_items;

items_sold
37

Counting with a condition

A powerful trick is to put a CASE inside an aggregate. This lets you count different groups in a single pass, in one row, like a small summary table.

SELECT
  SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS delivered,
  SUM(CASE WHEN status = 'shipped'   THEN 1 ELSE 0 END) AS shipped,
  SUM(CASE WHEN status NOT IN ('delivered', 'shipped') THEN 1 ELSE 0 END) AS other
FROM orders;

delivered | shipped | other
6         | 2       | 2

Each order adds 1 to one of the three columns and 0 to the others. This pattern, sometimes called a conditional count or pivot, is used constantly in reports.

Which date is the latest?

MIN and MAX work on dates stored as YYYY-MM-DD. The first and last order are easy to find.

SELECT MIN(order_date) AS first_order, MAX(order_date) AS last_order FROM orders;

first_order | last_order
2024-06-02  | 2024-08-05

Common mistakes

• Putting an aggregate in WHERE, such as WHERE COUNT(*) > 2. WHERE filters rows before counting. Use HAVING for conditions on aggregates (next lesson).
• Mixing an aggregate and a plain column without GROUP BY.
• Forgetting that COUNT(column) skips NULL while COUNT(*) does not.
• Dividing two whole numbers in some engines and getting a whole number back. In SQLite and SQL Server, 7 / 2 is 3. Write 7 / 2.0 or use CAST to get 3.5.

Your task

1. Count how many products there are in total, and how many cost more than 500.
2. Find the cheapest and the most expensive product price in the Stationery category.
3. Count how many different customers have placed an order (use COUNT(DISTINCT customer_id) on orders). The answer is 7.
4. Find the average price rounded to one decimal place.

How to check it worked

• There are 8 products and 4 cost more than 500.
• The Stationery prices run from 60 to 120.
• The rounded average price is 638.8.