When working with databases, it's often necessary to calculate the total or average of a numeric column. Whether you're tracking sales figures, employee salaries, or expenses, the SQL SUM() and AVG() functions provide an efficient way to perform these calculations.
The SUM() function adds up all the values in a numeric column, while the AVG() function computes the average value of the specified column. Both are aggregate functions, meaning they operate on a set of rows and return a single result. These functions are incredibly useful for analyzing data, making reports, and summarizing important metrics.
The SUM() function in SQL is used to return the total sum of a numeric column. It is commonly used in reports and summaries where you need to calculate the total value of items, such as the total sales, total revenue, or total quantity sold.
SELECT SUM(column_name)
FROM table_name;
column_name: The numeric column from which you want to calculate the sum.table_name: The table containing the data.Suppose you have a sales table with an amount column that contains the sales amount for each transaction. You can use the SUM() function to calculate the total sales amount:
SELECT SUM(amount) AS total_sales
FROM sales;
Result:
| total_sales |
|---|
| 500000 |
This query will return the total sales amount in the sales table.
SUM() only works with numeric columns (e.g., integers, decimals, etc.).NULL values in the column, they are ignored by SUM().The AVG() function in SQL is used to calculate the average (mean) of a numeric column. It is helpful when you need to find the average value of a column, such as the average salary, average price, or average age of customers.
SELECT AVG(column_name)
FROM table_name;
column_name: The numeric column from which you want to calculate the average.table_name: The table containing the data.Let’s say you want to calculate the average sales amount from the sales table. You can use the AVG() function like this:
SELECT AVG(amount) AS average_sale
FROM sales;
Result:
| average_sale |
|---|
| 2000 |
This query will return the average sale amount from the sales table.
AVG() works with numeric columns.AVG() ignores NULL values in the column.Both SUM() and AVG() can be used with the GROUP BY clause to calculate totals and averages for each group in your dataset. For example, you might want to calculate the total and average sales for each product or department.
SELECT column_name, SUM(column_name), AVG(column_name)
FROM table_name
GROUP BY column_name;
Let’s say you have an orders table with columns product_id and amount, and you want to calculate the total and average sales for each product. You can use the following query:
SELECT product_id, SUM(amount) AS total_sales, AVG(amount) AS average_sales
FROM orders
GROUP BY product_id;
Result:
| product_id | total_sales | average_sales |
|---|---|---|
| 101 | 15000 | 2500 |
| 102 | 20000 | 4000 |
| 103 | 10000 | 5000 |
This query groups the data by product_id and calculates the total sales (SUM(amount)) and the average sales (AVG(amount)) for each product.
You can also use the WHERE clause with the SUM() and AVG() functions to filter the data before performing the aggregation. This allows you to calculate the total or average only for rows that meet specific conditions.
Suppose you have a sales table, and you want to calculate the total and average sales for the year 2024. You can use the WHERE clause to filter the data based on the year:
SELECT SUM(amount) AS total_sales_2024, AVG(amount) AS average_sales_2024
FROM sales
WHERE YEAR(sale_date) = 2024;
Result:
| total_sales_2024 | average_sales_2024 |
|---|---|
| 250000 | 5000 |
This query returns the total and average sales for the year 2024 from the sales table.
Both SUM() and AVG() can be used with the DISTINCT keyword to perform calculations on unique values in a column. This is useful when you want to calculate the total or average of distinct values, excluding duplicates.
SELECT SUM(DISTINCT column_name), AVG(DISTINCT column_name)
FROM table_name;
Let’s say you have a sales table with a column amount, and some rows contain duplicate values for sales amounts. You can calculate the total and average sales, considering only distinct (unique) sales amounts:
SELECT SUM(DISTINCT amount) AS total_unique_sales, AVG(DISTINCT amount) AS average_unique_sales
FROM sales;
Result:
| total_unique_sales | average_unique_sales |
|---|---|
| 200000 | 3000 |
This query will sum up and calculate the average of only the unique sales amounts in the sales table.
Both SUM() and AVG() ignore NULL values. If a column contains NULL values, they do not affect the total or average calculation. However, it’s important to be aware of how NULL values behave when using these functions.
Suppose you have a products table with a price column, and some of the price values are NULL. If you want to calculate the total and average price, NULL values will be ignored:
SELECT SUM(price) AS total_price, AVG(price) AS average_price
FROM products;
If the price column contains NULL values, they will not be counted in the SUM() or AVG() calculation.
You can combine SUM() and AVG() with other aggregate functions like COUNT(), MAX(), and MIN() to perform more advanced calculations in a single query.
Suppose you want to calculate the total sales, average sales, and the number of sales transactions for each product. You can combine SUM(), AVG(), and COUNT() in a single query:
SELECT product_id,
SUM(amount) AS total_sales,
AVG(amount) AS average_sales,
COUNT(*) AS total_transactions
FROM sales
GROUP BY product_id;
Result:
| product_id | total_sales | average_sales | total_transactions |
|---|---|---|---|
| 101 | 5000 | 2500 | 2 |
| 102 | 8000 | 4000 | 2 |
| 103 | 12000 | 6000 | 2 |
This query calculates the total sales (SUM(amount)), average sales (AVG(amount)), and the total number of transactions (COUNT(*)) for each product.