COUNT, SUM, AVG, MIN and MAX

Opening your lesson

Aggregate functions turn a column of values into one value:

COUNT(*) counts rows. COUNT(column) counts rows where that column isn't NULL, and COUNT(DISTINCT column) counts different values.
SUM(column) adds up and AVG(column) takes the mean.
MIN(column) and MAX(column) find the smallest and largest. They work on numbers, text (A to Z) and dates.

SELECT COUNT(*) AS orders, SUM(total) AS revenue
FROM orders
WHERE status = 'delivered';

WHERE runs first, so only the rows that pass are counted and added. Without GROUP BY (the next lesson) you get one row for the whole table.

Averages often come with many decimal places; ROUND(value, 2) tidies them for a report. Every aggregate except COUNT(*) skips NULL values.

SELECT COUNT(*) AS products,
       MIN(price) AS cheapest,
       MAX(price) AS dearest,
       ROUND(AVG(price), 2) AS avg_price
FROM products;

Four aggregates in one row.

Choose your character