So far, we've learned how to retrieve individual rows, filter data, and perform calculations with expressions. But what if you want to answer questions like "How many orders do we have?" or "What's our total revenue?" These questions require summarizing data across multiple rows, which is where aggregate functions come in.
Aggregate functions perform calculations on a set of rows and return a single result. Think of them as powerful calculators that can process entire columns of data at once.
Aggregate functions take multiple input values and combine them into a single output value. Instead of showing you every individual row, they give you summary information about your entire dataset.
Common business questions aggregate functions can answer:
Sample Sales Data:
order_id | customer_id | order_amount | order_date
---------|-------------|--------------|------------
1 | 101 | 250.00 | 2024-01-15
2 | 102 | 175.50 | 2024-01-16
3 | 103 | 89.99 | 2024-01-17
4 | 101 | 320.00 | 2024-01-18
5 | 104 | 99.95 | 2024-01-19
Returns the number of rows in your result set.
Basic Syntax:
SELECT COUNT(*) FROM table_name;
SELECT COUNT(column_name) FROM table_name;
Examples:
-- Count total number of orders
SELECT COUNT(*) AS total_orders FROM sales;
-- Result: 5
-- Count orders with non-NULL customer_id
SELECT COUNT(customer_id) AS orders_with_customers FROM sales;
-- Result: 5
💡 Key Difference: COUNT(*) counts all rows, including NULLs. COUNT(column_name) counts only non-NULL values in that specific column.
Returns the total sum of a numerical column.
Basic Syntax:
SELECT SUM(column_name) FROM table_name;
Example:
-- Calculate total revenue
SELECT SUM(order_amount) AS total_revenue FROM sales;
-- Result: 935.44
Returns the average value of a numerical column.
Basic Syntax:
SELECT AVG(column_name) FROM table_name;
Example:
-- Calculate average order value
SELECT AVG(order_amount) AS average_order_value FROM sales;
-- Result: 187.09
Returns the smallest value in a column.
Basic Syntax:
SELECT MIN(column_name) FROM table_name;
Example:
-- Find lowest order amount
SELECT MIN(order_amount) AS lowest_order FROM sales;
-- Result: 89.99
Returns the largest value in a column.
Basic Syntax:
SELECT MAX(column_name) FROM table_name;
Example:
-- Find highest order amount
SELECT MAX(order_amount) AS highest_order FROM sales;
-- Result: 320.00
Just like with expressions, always use meaningful aliases for your aggregate results:
SELECT COUNT(*) AS total_records,
SUM(order_amount) AS total_revenue,
AVG(order_amount) AS average_order_value
FROM sales;
Result:
total_records | total_revenue | average_order_value
--------------|---------------|--------------------
5 | 935.44 | 187.09
You can use several aggregate functions in a single query to get a complete summary:
SELECT COUNT(*) AS total_orders,
MIN(order_amount) AS lowest_order,
MAX(order_amount) AS highest_order,
AVG(order_amount) AS average_order,
SUM(order_amount) AS total_revenue
FROM sales;
Result:
total_orders | lowest_order | highest_order | average_order | total_revenue
-------------|--------------|---------------|---------------|---------------
5 | 89.99 | 320.00 | 187.09 | 935.44
You can filter data before aggregating using the WHERE clause:
-- Get statistics for orders over $100
SELECT COUNT(*) AS large_orders,
SUM(order_amount) AS large_order_revenue,
AVG(order_amount) AS large_order_average
FROM sales
WHERE order_amount > 100;
Result:
large_orders | large_order_revenue | large_order_average
-------------|---------------------|--------------------
3 | 745.50 | 248.50
💡 Important: The WHERE clause filters rows before the aggregate function processes them.
Understanding how aggregate functions handle NULL values is crucial:
Example with NULL values:
-- Sample data with NULL
-- order_amount: 100, 200, NULL, 300
SELECT COUNT(*) AS all_rows, -- Result: 4
COUNT(order_amount) AS non_null, -- Result: 3
SUM(order_amount) AS total, -- Result: 600
AVG(order_amount) AS average -- Result: 200
FROM sales_with_nulls;
Aggregate functions are perfect for:
Real-world scenarios:
-- Monthly sales summary
SELECT COUNT(*) AS orders_this_month,
SUM(order_amount) AS monthly_revenue,
AVG(order_amount) AS avg_order_value,
MAX(order_amount) AS largest_sale
FROM sales
WHERE order_date >= '2024-01-01';
Instead of looking at individual rows, aggregate functions let you answer:
-- "How many customers do we have?"
SELECT COUNT(*) AS total_customers FROM customers;
-- "What's our total revenue?"
SELECT SUM(order_amount) AS total_revenue FROM sales;
-- "What's the average order value?"
SELECT AVG(order_amount) AS avg_order_value FROM sales;
-- "What's our highest sale?"
SELECT MAX(order_amount) AS highest_sale FROM sales;
-- "What's our smallest order?"
SELECT MIN(order_amount) AS smallest_order FROM sales;
💡 Did You Know? Aggregate functions are incredibly efficient! Instead of retrieving thousands of rows and calculating totals in your application, the database does all the work and returns just one number. This saves both time and memory, especially with large datasets. Different databases might have additional aggregate functions, but COUNT, SUM, AVG, MIN, and MAX are supported by all major SQL databases.
In this lesson's exercises, you'll work with a Sales table containing transaction data. You'll practice counting records, calculating totals, finding averages, and identifying minimum and maximum values.
These practical scenarios will help you understand how aggregate functions provide valuable business insights from raw data. You'll work with:
Take your time to understand how these functions transform rows of data into meaningful business metrics!
Ready to practice SQL? Try our coding challenges →