SQL Tutorial · Aggregation and Functions · Lesson 13 of 36

SQL Aggregate Functions

COUNT, SUM, AVG, MIN and MAX.

Summarising data

Aggregate functions compute one value from many rows:

Function	Returns
`COUNT(*)`	number of rows
`COUNT(col)`	number of non-NULL values
`SUM(col)`	total
`AVG(col)`	average
`MIN(col)` / `MAX(col)`	smallest / largest

Example 1Try it yourself

SELECT COUNT(*) AS products, MIN(price) AS cheapest, MAX(price) AS priciest,
       ROUND(AVG(price), 2) AS avg_price, SUM(stock) AS units_in_stock
FROM products;

Output

products | cheapest | priciest | avg_price | units_in_stock
12 | 5.5 | 329 | 69.57 | 495

With WHERE

WHERE filters rows before they're aggregated:

Example 2Try it yourself

SELECT COUNT(*) AS delivered_orders FROM orders WHERE status = 'delivered';
SELECT SUM(quantity * unit_price) AS revenue FROM order_items WHERE order_id = 7;

Output

delivered_orders
7

revenue
114.99

Test yourself

Does COUNT(email) count rows where email is NULL?

Yes
No

Show answer

Answer: No