COUNT, SUM, AVG, MIN, MAX

“How many students are there?”, “what is the average score?”, “how much does the most expensive product cost?” — questions like these turn many rows into one number. That is what aggregate functions are for.

The five main functions

SELECT COUNT(*) AS students FROM students;

SELECT MIN(score), MAX(score), ROUND(AVG(score), 1) FROM grades;

Together with WHERE

Rows are picked first, then counted:

SELECT COUNT(*) FROM students WHERE funding = 'grant';

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

• COUNT(*) — every row;
• COUNT(phone) — only rows where phone is not empty;
• COUNT(DISTINCT city) — how many different cities.

SELECT COUNT(*) AS all_rows, COUNT(phone) AS with_phone, COUNT(DISTINCT city) AS cities FROM students;

Aggregates ignore NULL: AVG(scholarship) gives the average only over students who receive one.

An important rule

You cannot just put an aggregate next to a plain column:

SELECT city, COUNT(*) FROM students;   -- error!

The database does not know which city to show: the result is one row, but there are many cities. To count each city separately you need GROUP BY — the next lesson.

Remember

• COUNT, SUM, AVG, MIN, MAX — one value from many rows.
• Aggregates skip NULL; COUNT(*) counts every row.
• Need a plain column and an aggregate together? Use GROUP BY.