Fraser Hamilton

How to get the average of a column in SQL

August 07, 2020

Let’s say we have a database table named orders with columns: id, name, and cost.

id name cost
1 John Doe 134.00
2 Eve Marcus 146.00
3 Lucy Tomlin 20.00
4 Bill Carson 118.00
5 John Doe 102.00
6 Lucy Tomlin 90.00
7 Lucy Tomlin 34.00
8 Bill Carson 122.00

In our application we might want to find the average cost of all orders, this is a perfect use case for the AVG aggregate function. Here’s what our query would look like:

SELECT AVG(cost) as average_cost FROM orders

And here’s the result:

average_cost
95.75

But we can take this even further, what if we wanted to get an average of each persons orders? We can alter our select to include the order name and then grouping on it. So our new query looks like this:

SELECT name, AVG(cost) as average_cost FROM orders GROUP BY name;

And this is our result:

name average_cost
John Doe 118.00
Eve Marcus 146.00
Lucy Tomlin 48.00
Bill Carson 120.00

Written by Fraser Hamilton a full stack developer based out of Edinburgh, Scotland.

© 2020, Fraser Hamilton