MySQL SUM() Function

The SUM() function adds up all the values in a numeric column and returns the total as a single number. It is an essential aggregate function for any kind of financial reporting, inventory management, or statistical summary — think total revenue, total items sold, or total quantity in stock.

---

Syntax

SELECT SUM(column_name) AS alias FROM table_name;

---

Sample Table

id	name	category	price	quantity_sold
1	Laptop	Electronics	55000.00	5
2	Phone	Electronics	25000.00	12
3	T-Shirt	Clothing	800.00	40
4	Headphones	Electronics	3500.00	18
5	Jeans	Clothing	2200.00	22
6	Tablet	Electronics	35000.00	8
7	Jacket	Clothing	4500.00	15

---

Basic SUM()

Get the total number of units sold across all products:

SELECT SUM(quantity_sold) AS total_sold FROM products;
-- Returns: 120

Get the sum of all product prices:

SELECT SUM(price) AS total_price_value FROM products;
-- Returns: 126000.00

---

SUM() with WHERE

Calculate the total only for a filtered subset of rows:

-- Total units sold for Electronics only
SELECT SUM(quantity_sold) AS electronics_sold
FROM products
WHERE category = 'Electronics';
-- Returns: 43 (5 + 12 + 18 + 8)

-- Total revenue from products priced above 10000
SELECT SUM(price) AS high_value_total
FROM products
WHERE price > 10000;
-- Returns: 115000.00

---

SUM() with Expressions

You can use SUM() on a calculated expression — for example, to get total revenue (price × quantity sold):

SELECT SUM(price * quantity_sold) AS total_revenue
FROM products;

total_revenue
1,267,100.00

---

SUM() with GROUP BY

Get the total sold per category using GROUP BY:

SELECT category, SUM(quantity_sold) AS total_sold
FROM products
GROUP BY category;

category	total_sold
Electronics	43
Clothing	77

---

SUM() and NULL Values

SUM() automatically ignores NULL values — they are treated as if the row does not exist for the calculation. If a quantity_sold cell were NULL, that row would simply be skipped.

-- Ensure 0 is returned instead of NULL when no rows match
SELECT COALESCE(SUM(quantity_sold), 0) AS total_sold
FROM products
WHERE category = 'Furniture';

---

Key Points to Remember

• SUM() adds all non-null values in a numeric column.
• Use WHERE to filter which rows are included in the sum.
• You can pass an expression to SUM() — for example, SUM(price * qty).
• Combine with GROUP BY to get separate totals per group.
• NULL values are ignored; if all values are NULL, the result is NULL — use COALESCE(SUM(...), 0) to handle this.