Adding Up with SUM and AVG
Totals and averages.
Helen wants two quick numbers for her accountant. How many books are on the shelves in total? And what's the average price of a book? Two new aggregate functions answer these. Sum, and average.

- How many books on the shelves?
- What's the average price?
- Meet SUM and AVG
SUM and AVG
Sum adds up a column, across all the rows. AVG works out the average. Averages often have long decimals, so we wrap it in round. Run it. Eight hundred and fifty five books on the shelves, and the average price is eighteen eleven.
SELECT SUM(stock) AS books_on_shelves,
ROUND(AVG(price), 2) AS average_price
FROM books;โโโโโโโโโโโโโโโโโโโโฌโโโโโโโโโโโโโโโโ โ books_on_shelves โ average_price โ โโโโโโโโโโโโโโโโโโโโผโโโโโโโโโโโโโโโโค โ 855 โ 18.11 โ โโโโโโโโโโโโโโโโโโโโดโโโโโโโโโโโโโโโโ
Copies sold
Here's a bigger one. Every row in order items says how many copies of a book were in an order. Add up the quantity column, and you get the total number of copies ever sold. Run it. Two thousand nine hundred and forty nine copies.
SELECT SUM(quantity) AS copies_sold
FROM order_items;โโโโโโโโโโโโโโโ โ copies_sold โ โโโโโโโโโโโโโโโค โ 2949 โ โโโโโโโโโโโโโโโ
How aggregates think
Here's the key idea. An aggregate function takes a whole column of values, and squashes it into one value. Many rows go in, one row comes out. Count counts them, sum adds them, average averages them. And nulls are skipped by all of them, except count star.
Summary
Sum adds, average averages. Next, the smallest and the largest.
Key takeaways- SUM(col) adds up
- AVG(col) averages
- Round long decimals