Learn SQL: AVG
Averages with AVG
"Are our customers happy?" asks Lien. "And how long do they keep a bike on average?" Averages, then. With a few catches.
AVG calculates the average
AVG (average) adds up the values and divides by how many there are. This is the average day price of all the bikes.
An average is rarely a round number. That isn't a mistake: SQL doesn't round unless you ask it to.
SELECT AVG(day_price) AS average_day_price FROM bikes| average_day_price |
|---|
| 21.625 |
AVG skips NULL
Just like COUNT(column) and SUM, AVG only looks at values that are filled in. So the average rating is the sum divided by 24 ratings, not by all 30 rentals.
That's usually what you want: someone who left it blank didn't give a zero.
SELECT AVG(rating) AS average, SUM(rating) AS total, COUNT(rating) AS how_many FROM rentals| average | total | how_many |
|---|---|---|
| 4.125 | 99 | 24 |
Pitfall, dividing whole numbers
You can also work out an average yourself with SUM(...) / COUNT(...). But if both are whole numbers, SQLite does whole-number division too and throws the remainder away: 7 / 2 gives 3.
AVG doesn't have that problem. Here you see the difference for the rental length of the e-bikes.
SELECT SUM(days) / COUNT(*) AS divided_yourself, AVG(days) AS with_avg FROM rentals WHERE bike_type = 'e-bike'| divided_yourself | with_avg |
|---|---|
| 3 | 3.1 |
Try it yourself
What is the average rating of the e-bike rentals?
Ready for more?
The full lesson has 6 exercises, from easy to challenging, with hints, points and a streak you can build.
Start the lesson