COUNT, SUM, AVG, MIN, MAX

2 min read 6 runnable examples Quiz included

Aggregate functions turn many rows into a single value: how many, how much, the average, the extremes.

Function	Returns
`COUNT(*)`	the number of rows
`COUNT(column)`	the number of non-NULL values in a column
`SUM(column)`	the total
`AVG(column)`	the average
`MIN(column)` / `MAX(column)`	the smallest / largest value

Counting

SQL

SELECT COUNT(*) AS total_customers
FROM customers;

Combine with WHERE to count only some rows:

SQL

SELECT COUNT(*) AS indian_customers
FROM customers
WHERE country = 'India';

Sum, average, minimum, maximum

SQL

SELECT
  SUM(stock)            AS units_in_stock,
  ROUND(AVG(price), 2)  AS average_price,
  MIN(price)            AS cheapest,
  MAX(price)            AS most_expensive
FROM products;

COUNT and NULL

COUNT(*) counts rows; COUNT(column) skips NULLs. One customer has no joined date:

SQL

SELECT COUNT(*) AS rows_total, COUNT(joined) AS with_join_date
FROM customers;

All aggregates except COUNT(*) ignore NULL values.

Counting distinct values

SQL

SELECT COUNT(DISTINCT country) AS countries
FROM customers;

Total value of stock

Aggregates work on expressions too:

SQL

SELECT ROUND(SUM(price * stock), 2) AS stock_value
FROM products;

Finished this lesson?Mark it complete to earn +20 XP and move your progress on.