SUM and AVG Functions
What you'll learn: How to compute totals and calculate averages of numeric data stored in database columns using SQL's SUM and AVG aggregate functions.
The Core Idea
When working with databases, you often need to answer questions like "What's the total revenue?" or "What's the average age of our users?" Rather than fetching all rows and calculating these values manually in your application code, SQL provides built-in aggregate functions that perform these calculations directly on the database server.
SUM adds up all numeric values in a column, giving you a total. AVG divides that sum by the count of non-NULL values, giving you the average (mean).
How They Work
Both functions operate on a column of numeric data. Think of SUM as a calculator that keeps a running total as it reads through each row, while AVG does the same but also counts how many numbers it added, then divides at the end.
-- Get total sales across all orders
SELECT SUM(order_amount) FROM orders;
-- Get average product price
SELECT AVG(price) FROM products;
-- You can use both together
SELECT SUM(salary) AS total_payroll,
AVG(salary) AS average_salary
FROM employees;
Important Notes
- Both functions ignore NULL values completely
- SUM returns NULL if all values are NULL or no rows match
- AVG divides by the count of non-NULL values only
- These work best with numeric data types (integers, decimals)
- You can combine them with WHERE clauses to filter which rows to aggregate
Key Takeaway: SUM computes totals and AVG computes averages for numeric columns, enabling powerful data analysis directly within your SQL queries without retrieving all individual rows.