Aggregate Functions

Up to this point, our queries have returned individual rows. If we queried 10 users, we got 10 rows back. But what if we don’t care about the individual rows? What if we want to answer questions like:

• “How many users signed up today?”
• “What is the average price of our products?”
• “What is the total revenue from this month?”

To answer these questions, we use Aggregate Functions. These functions take multiple rows of data and “aggregate” them down into a single summary row.

The Core Functions

SQL provides five standard aggregate functions:

1. COUNT(): Returns the number of rows.
2. SUM(): Adds up the values in a numeric column.
3. AVG(): Calculates the average of a numeric column.
4. MIN(): Finds the lowest value in a column.
5. MAX(): Finds the highest value in a column.

Using COUNT

COUNT is the most commonly used aggregate. If you want to know the total number of items ever sold in our order_items table, you can do:

SELECT COUNT(*) FROM order_items;

COUNT(*) simply counts the number of rows. If you pass a specific column name instead, like COUNT(email), it will count the number of rows where the email column is not NULL.

Using SUM and AVG

For financial or quantitative data, SUM and AVG are essential. If we want to find the average price of items sold:

SELECT AVG(unit_price) FROM order_items;

You can even perform mathematics inside the aggregate function! For example, unit_price by itself isn’t the total value if the user bought 5 of them. To get the true value of an order line, we multiply quantity by price:

SELECT SUM(quantity * unit_price) FROM order_items;

[!WARNING] You cannot mix aggregate columns and normal columns in a standard SELECT statement without using GROUP BY (which we cover in the next lesson).

❌ Incorrect: SELECT product_id, SUM(quantity) FROM order_items; The database won’t know which product_id to display alongside the single total sum.

Try using SUM in the lab to calculate the total lifetime revenue of our E-Commerce store!