AI Hints
Homeโ€บSQL Tutorialโ€บCounting and Totals
SQL ยท Lesson 32 of 126

Adding Up with SUM and AVG

Totals and averages.

โฑ 2 min readโ–ถ Video at 45:21๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

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.

Adding Up with SUM and AVG
From the video: Adding Up with SUM and AVG
  • 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.

query.sqlsql
SELECT SUM(stock)            AS books_on_shelves,
       ROUND(AVG(price), 2)  AS average_price
FROM books;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ 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.

query.sqlsql
SELECT SUM(quantity) AS copies_sold
FROM order_items;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚ 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.

Many rows ina whole column
One row outa single answer
NULLsskipped
ExceptCOUNT(*)

Summary

Sum adds, average averages. Next, the smallest and the largest.

Key takeaways
  • SUM(col) adds up
  • AVG(col) averages
  • Round long decimals
Next up: Smallest and Largest โ€” MIN and MAX