Counting, Summing, and Averaging with COUNT, SUM, and AVG

So far every query has returned a list of rows. Sometimes though, what you actually want is a single number that summarizes many rows at once: how many users signed up, how much money a customer has spent in total, what the average product price is. These are called aggregate functions.

COUNT: how many rows

SELECT COUNT(*) FROM users;

This returns one number: the total number of rows in the users table, in other words, how many users you have. You can combine it with WHERE just like any other SELECT:

SELECT COUNT(*) FROM posts WHERE author = 'ana';

This counts only the posts written by ana, ignoring everyone else's posts entirely.

SUM: adding a column up

SELECT SUM(price) FROM orders WHERE customer_id = 3;

This adds together the price column across every matching row, giving you the total amount customer 3 has spent, as one single number, instead of a long list you would have to add up yourself.

AVG: the average value

SELECT AVG(price) FROM products;

This adds up every price in the products table and divides by how many products there are, giving you the average product price in one step.

Where you will see this on a real website

A shop's admin dashboard might show the total number of orders today with COUNT, the total revenue with SUM, and the average order value with AVG. A blog might show how many comments a post has received using COUNT. These small numbers, often shown in a dashboard or a sidebar, are almost always powered by a single aggregate query like the ones above.