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;

-- Get the total and average salary of all employees.
SELECT SUM(salary) AS total_salary, AVG(salary) AS avg_salary FROM employees;

-- Get the total and average salary per department.
SELECT department, SUM(salary) AS total_salary, AVG(salary) AS avg_salary
FROM employees
GROUP BY department
ORDER BY total_salary DESC;

-- Round the average salary to 2 decimal places.
SELECT ROUND(AVG(salary), 2) AS avg_salary FROM employees;

-- Total bonus (use IFNULL to treat NULL as 0).
SELECT SUM(IFNULL(bonus, 0)) AS total_bonus FROM employees;

-- Get the total sales amount for products.
SELECT SUM(price * quantity) AS total_sales FROM order_items;

-- Result of: 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 |
-- +------------+--------------+------------+
-- | Dev        |  2400000     | 300000.000 |
-- | Sales      |  1680000     | 280000.000 |
-- | Admin      |   900000     | 300000.000 |
-- +------------+--------------+------------+

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.