This lecture covers the SUM() and AVG() aggregate functions.
After counting rows, the next most common summary tasks are calculating the total and the average of a set of numeric values. For this, SQL provides two more aggregate functions: SUM() and AVG().
These functions only work on columns that contain numeric data (like INTEGER or REAL).
Calculating the Total with `SUM()`
The SUM() function adds up all the values in a specified column. It’s perfect for answering questions like “What is the total revenue from all sales?”
Let’s use our `Sales` table from the last lecture. To find the total amount of money we’ve made from all sales, we can sum the `Price` column.
-- Let's use the same Sales table
-- Apple (0.50), Banana (0.30), Apple (0.50), Orange (0.60)
SELECT SUM(Price) AS TotalRevenue FROM Sales;
This query will add up 0.50 + 0.30 + 0.50 + 0.60 and return a single value: 1.90.
Calculating the Average with `AVG()`
The AVG() function calculates the average of all the values in a specified column. It answers questions like “What was the average price of an item sold?”
Using the same `Sales` table, let’s find the average sale price.
SELECT AVG(Price) AS AverageSalePrice FROM Sales;
This query will add up all the prices (1.90) and divide by the number of sales (4), returning a single value: 0.475.
Combining with `WHERE`
Just like COUNT(), both SUM() and AVG() can be combined with a WHERE clause to perform calculations on a subset of your data.
For example, what is the total revenue just from selling Apples?
SELECT SUM(Price) AS AppleRevenue FROM Sales WHERE ItemName = 'Apple';This will sum the prices only for the rows where the item was an Apple, returning 1.00.
What was the average price of a piece of fruit that wasn’t a Banana?
SELECT AVG(Price) AS AvgPriceOfNonBanana FROM Sales WHERE ItemName != 'Banana';In the Next Lecture…
We will look at two more simple but useful aggregate functions, MIN() and MAX(), which allow us to easily find the minimum and maximum values in a column.
