Aggregate functions

An aggregate function turns many rows into one value: how many orders there are, the total amount, the highest salary. Testers use them all the time to compare what an application shows (a total on a dashboard, a number next to an icon, a report) with what the database holds. Interviewers ask about them because the details are easy to get wrong: what COUNT counts, what happens to NULLs, and what comes back when no row matches. The examples use the company data.

The five you must know

COUNT counts rows or values. SUM adds up a numeric column and AVG gives its average. MIN and MAX give the smallest and largest value. Without GROUP BY (the next topic, which makes one result per group of rows), an aggregate query returns exactly one row, whatever the size of the table:

SELECT COUNT(*), MAX(salary), MIN(salary), ROUND(AVG(salary)) FROM employees;

COUNT(*) | MAX(salary) | MIN(salary) | ROUND(AVG(salary))
10 | 150000 | 47000 | 77700

ROUND(x) rounds to a whole number and ROUND(x, 2) to two decimal places. AVG of whole numbers often gives a decimal, so round it before you compare it with a value shown on screen.

COUNT(*), COUNT(column) and COUNT(DISTINCT column)

This is the most asked aggregate question. COUNT(*) counts rows. COUNT(amount) counts rows where amount is not NULL. COUNT(DISTINCT customer) counts the different non-NULL values.

SELECT COUNT(*) AS orders, COUNT(amount) AS with_amount,
       COUNT(DISTINCT customer) AS customers
FROM orders;

orders | with_amount | customers
10 | 9 | 6

There are ten orders, but one has no amount, and several customers ordered more than once. When a test checks that "the report shows 10 orders", make sure you know which of the three counts the report means.

NULLs and empty results

Every aggregate on a column skips NULLs, so AVG is the average of the known values only. Two more rules surprise people. SUM, AVG, MIN and MAX over no rows give NULL, while COUNT gives 0. And the query still returns one row, even when WHERE matches nothing:

SELECT SUM(amount), COUNT(*) FROM orders WHERE status = 'RETURNED';

SUM(amount) | COUNT(*)
NULL | 0

If your test expects 0 for "no returned orders", wrap the sum: COALESCE(SUM(amount), 0).

MIN and MAX on text and dates

MIN and MAX work on any type that can be sorted. On text they use alphabetical order. On dates stored as YYYY-MM-DD text they give the earliest and latest date, because that format sorts in date order:

SELECT MIN(hire_date), MAX(hire_date), MIN(name) FROM employees;

MIN(hire_date) | MAX(hire_date) | MIN(name)
2018-03-01 | 2025-04-14 | Arjun Das

Counting by condition

To get several counts in one row, put a CASE inside SUM. Each row adds 1 when it matches and 0 when it does not. This gives a quick summary of order states or test results in one query:

SELECT SUM(CASE WHEN status = 'DELIVERED' THEN 1 ELSE 0 END) AS delivered,
       SUM(CASE WHEN status = 'CANCELLED' THEN 1 ELSE 0 END) AS cancelled,
       SUM(CASE WHEN status = 'DELIVERED' THEN amount ELSE 0 END) AS delivered_amount,
       COUNT(*) AS total
FROM orders;

delivered | cancelled | delivered_amount | total
5 | 1 | 39000 | 10

PostgreSQL and SQLite (3.30 and later) also offer COUNT(*) FILTER (WHERE status = 'DELIVERED'), but the CASE form works in every database.

A plain column next to an aggregate

Candidates often write SELECT name, MAX(salary) FROM employees to find the best-paid person. Standard SQL does not allow this. The result is one row, so which name should it show? PostgreSQL rejects the query, and so does MySQL in its default ONLY_FULL_GROUP_BY mode (a setting that makes MySQL strict about this). SQLite accepts it and, for a single MIN or MAX, takes the other columns from the row that holds the largest or smallest value. Do not rely on that. The portable answer works in every database. It uses a subquery (a query inside another query, see Subqueries) and also handles ties, which means two people with the same top salary:

SELECT name, salary FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees);

name | salary
Asha Rao | 150000

How to answer in an interview

When asked "COUNT(*) or COUNT(column)?", answer in one line: COUNT(*) counts rows; COUNT(column) counts rows where that column is not NULL. Then give an example from the data, such as 10 orders but 9 amounts. For "who earns the most?", write the subquery form and mention that it returns every person who shares the top salary.

Common mistakes

• Using COUNT(column) when you mean "how many rows".
• Expecting SUM of nothing to be 0 instead of NULL.
• Comparing an unrounded average with a rounded number on screen.
• Selecting a plain column next to MAX to get "the row with the maximum".

Recap

Aggregates skip NULLs; COUNT(*) does not. With no matching rows, COUNT gives 0 and the others give NULL. Use SUM with CASE to count by condition, and a subquery to find the row behind a MAX. The next topic, GROUP BY, applies the same functions to each group of rows instead of the whole table.