SUM / AVG

Returns the sum of a column.

SELECT SUM(column_name) FROM table_name;

Returns the average of a column.

SELECT AVG(column_name) FROM table_name;

Returns the sum and average of distinct (deduplicated) values.

SELECT SUM(DISTINCT column_name), AVG(DISTINCT column_name) FROM table_name;

Returns the sum and average per group.

SELECT group_column, SUM(column_name), AVG(column_name) FROM table_name GROUP BY group_column;

The following examples use the employees table.

Get the total and average salary of all employees.

sample_sum_avg.sql

SELECT SUM(salary) AS total_salary, AVG(salary) AS avg_salary FROM employees;

+--------------+------------+
| total_salary | avg_salary |
+--------------+------------+
|      1160000 | 290000.000 |
+--------------+------------+
1 row in set

Get the total and average salary per department.

sample_sum_avg.sql

SELECT department, SUM(salary) AS total_salary, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
ORDER BY total_salary DESC;

+---------------+--------------+------------+
| department    | total_salary | avg_salary |
+---------------+--------------+------------+
| Capsule Corp  |       600000 | 300000.000 |
| Turtle School |       560000 | 280000.000 |
+---------------+--------------+------------+
2 rows in set

Round the average salary to 2 decimal places.

sample_sum_avg.sql

SELECT ROUND(AVG(salary), 2) AS avg_salary FROM employees;

+------------+
| avg_salary |
+------------+
|  290000.00 |
+------------+
1 row in set

Get the total bonus (use IFNULL to treat NULL as 0).

sample_sum_avg.sql

SELECT SUM(IFNULL(bonus, 0)) AS total_bonus FROM employees;

+-------------+
| total_bonus |
+-------------+
|       90000 |
+-------------+
1 row in set

Count employees who receive a bonus and those who do not.

sample_sum_avg.sql

SELECT
    SUM(CASE WHEN bonus IS NOT NULL THEN 1 ELSE 0 END) AS with_bonus,
    SUM(CASE WHEN bonus IS NULL THEN 1 ELSE 0 END) AS without_bonus
FROM employees;

+------------+---------------+
| with_bonus | without_bonus |
+------------+---------------+
|          2 |             2 |
+------------+---------------+
1 row in set

The basic syntax of SUM and AVG is consistent across all major databases.

SELECT department, SUM(salary) AS total_salary, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

The function name for treating NULL as 0 differs by database. MySQL uses IFNULL, while PostgreSQL, Oracle, SQL Server, and SQLite use COALESCE (which is also supported in MySQL).

-- MySQL
SELECT SUM(IFNULL(bonus, 0)) AS total_bonus FROM employees;

-- PostgreSQL, Oracle, SQL Server, SQLite (also works in MySQL)
SELECT SUM(COALESCE(bonus, 0)) AS total_bonus FROM employees;

Rounding decimal places uses the same ROUND(value, digits) syntax across MySQL, PostgreSQL, SQLite, Oracle, and SQL Server.

Both SUM and AVG automatically ignore NULL values in their calculations. If you want NULL to be treated as 0 and included in the sum, you must explicitly convert it using IFNULL(column_name, 0) or COALESCE(column_name, 0).

If no rows match (e.g., no rows satisfy the WHERE condition), both SUM and AVG return NULL. To safely return 0 instead, wrap the call as IFNULL(SUM(column_name), 0).

To find maximum or minimum values, see MAX / MIN. For filtering grouped results, see GROUP BY / HAVING.