How to Use SUM in MySQL
The SUM function is MySQL's workhorse for calculating totals across numeric columns. As an aggregate function, it processes multiple rows and returns a single value—the sum of all input values....
Key Insights
- The SUM function aggregates numeric values but returns NULL if all input values are NULL, while treating individual NULL values as zero in calculations—understanding this behavior prevents unexpected results in production queries.
- Combining SUM with CASE statements enables powerful conditional aggregation, letting you calculate multiple business metrics in a single query pass rather than executing separate queries for each metric.
- Proper indexing on columns used in WHERE, GROUP BY, and JOIN clauses can reduce SUM query execution time from minutes to milliseconds, especially on tables with millions of rows.
Introduction to the SUM Function
The SUM function is MySQL’s workhorse for calculating totals across numeric columns. As an aggregate function, it processes multiple rows and returns a single value—the sum of all input values. You’ll use SUM constantly for financial reporting, analytics dashboards, inventory management, and any scenario requiring numeric totals.
Unlike scalar functions that operate on individual values, SUM operates on sets of rows. This makes it essential for business intelligence queries where you need to answer questions like “What’s our total revenue?” or “How many units shipped this quarter?”
Here’s the basic syntax:
SELECT SUM(column_name) FROM table_name;
Let’s see a practical example with an orders table:
SELECT SUM(order_total) AS total_revenue
FROM orders;
This returns a single value: the sum of all order totals in your database.
Basic SUM Usage
The simplest SUM operations calculate totals for a single column. Here’s a realistic scenario with a sales table:
CREATE TABLE sales (
id INT PRIMARY KEY,
product_name VARCHAR(100),
quantity INT,
price DECIMAL(10,2),
sale_date DATE
);
-- Calculate total quantity sold
SELECT SUM(quantity) AS total_units_sold
FROM sales;
You’ll often need filtered sums. Use WHERE clauses to limit which rows contribute to the calculation:
-- Sum only sales from the current year
SELECT SUM(price * quantity) AS yearly_revenue
FROM sales
WHERE YEAR(sale_date) = YEAR(CURDATE());
-- Sum sales for a specific product
SELECT SUM(quantity) AS product_total
FROM sales
WHERE product_name = 'Laptop Pro 15';
Handling NULL Values
MySQL’s NULL handling in SUM can trip you up. The function ignores NULL values in calculations, treating them as if they don’t exist. However, if ALL values are NULL, SUM returns NULL rather than zero:
-- If some quantities are NULL, they're ignored
SELECT SUM(quantity) FROM sales;
-- Result: 150 (NULLs excluded)
-- If ALL quantities are NULL
SELECT SUM(quantity) FROM empty_sales;
-- Result: NULL (not 0)
-- Force zero instead of NULL
SELECT COALESCE(SUM(quantity), 0) AS total
FROM sales
WHERE product_name = 'Nonexistent Product';
-- Result: 0
Always use COALESCE when you need zero instead of NULL for business logic or reporting.
Using SUM with GROUP BY
GROUP BY transforms SUM from a single total into subtotals for each category. This is where SUM becomes truly powerful for analysis:
-- Sum sales by product
SELECT
product_name,
SUM(quantity) AS total_quantity,
SUM(price * quantity) AS total_revenue
FROM sales
GROUP BY product_name;
You can group by multiple columns for dimensional analysis:
-- Sales by product and month
SELECT
product_name,
DATE_FORMAT(sale_date, '%Y-%m') AS month,
SUM(quantity) AS monthly_quantity,
SUM(price * quantity) AS monthly_revenue
FROM sales
GROUP BY product_name, DATE_FORMAT(sale_date, '%Y-%m')
ORDER BY month DESC, monthly_revenue DESC;
The HAVING clause filters grouped results based on aggregate values. Unlike WHERE (which filters before grouping), HAVING filters after aggregation:
-- Find products with total revenue over $10,000
SELECT
product_name,
SUM(price * quantity) AS total_revenue
FROM sales
GROUP BY product_name
HAVING SUM(price * quantity) > 10000
ORDER BY total_revenue DESC;
-- Products sold in quantities over 100 units
SELECT
product_name,
SUM(quantity) AS total_quantity
FROM sales
GROUP BY product_name
HAVING SUM(quantity) > 100;
Advanced SUM Techniques
Conditional aggregation with CASE statements lets you calculate multiple metrics simultaneously:
-- Calculate revenue by category in a single query
SELECT
SUM(CASE WHEN product_name LIKE '%Laptop%' THEN price * quantity ELSE 0 END) AS laptop_revenue,
SUM(CASE WHEN product_name LIKE '%Desktop%' THEN price * quantity ELSE 0 END) AS desktop_revenue,
SUM(CASE WHEN product_name LIKE '%Tablet%' THEN price * quantity ELSE 0 END) AS tablet_revenue,
SUM(price * quantity) AS total_revenue
FROM sales;
This pattern is incredibly efficient—one table scan instead of three separate queries.
SUM with DISTINCT counts each unique value only once:
-- Sum unique order amounts (ignore duplicates)
SELECT SUM(DISTINCT order_total) FROM orders;
-- This is different from:
SELECT SUM(order_total) FROM orders;
Use DISTINCT sparingly with SUM—it’s rarely what you actually need and forces MySQL to sort and deduplicate, hurting performance.
Combine SUM with other aggregate functions for richer analytics:
SELECT
product_name,
COUNT(*) AS number_of_sales,
SUM(quantity) AS total_quantity,
AVG(price) AS average_price,
SUM(price * quantity) AS total_revenue,
SUM(price * quantity) / COUNT(*) AS average_sale_value
FROM sales
GROUP BY product_name;
SUM in subqueries enables comparisons against totals:
-- Find products contributing more than 10% of total revenue
SELECT
product_name,
SUM(price * quantity) AS product_revenue,
(SUM(price * quantity) / (SELECT SUM(price * quantity) FROM sales)) * 100 AS revenue_percentage
FROM sales
GROUP BY product_name
HAVING product_revenue > (SELECT SUM(price * quantity) * 0.1 FROM sales);
SUM with JOINs
Real-world databases spread data across multiple tables. JOINs let you aggregate across relationships:
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(100),
region VARCHAR(50)
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_total DECIMAL(10,2),
order_date DATE
);
-- Sum orders by customer
SELECT
c.customer_name,
c.region,
COUNT(o.order_id) AS order_count,
SUM(o.order_total) AS total_spent
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name, c.region
ORDER BY total_spent DESC;
LEFT JOINs require careful NULL handling:
-- Include customers with no orders
SELECT
c.customer_name,
COALESCE(SUM(o.order_total), 0) AS total_spent,
COALESCE(COUNT(o.order_id), 0) AS order_count
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id, c.customer_name;
Without COALESCE, customers with no orders show NULL instead of 0.
Multi-table aggregation for complex reporting:
CREATE TABLE order_items (
item_id INT PRIMARY KEY,
order_id INT,
product_id INT,
quantity INT,
unit_price DECIMAL(10,2)
);
-- Calculate customer lifetime value with detailed breakdown
SELECT
c.customer_name,
COUNT(DISTINCT o.order_id) AS order_count,
SUM(oi.quantity) AS total_items_purchased,
SUM(oi.quantity * oi.unit_price) AS lifetime_value
FROM customers c
INNER JOIN orders o ON c.customer_id = o.customer_id
INNER JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY c.customer_id, c.customer_name
HAVING lifetime_value > 1000
ORDER BY lifetime_value DESC;
Performance Considerations and Best Practices
SUM queries can become slow on large tables without proper optimization. Indexes are your first line of defense:
-- Index columns used in WHERE clauses
CREATE INDEX idx_sale_date ON sales(sale_date);
-- Index columns used in GROUP BY
CREATE INDEX idx_product_name ON sales(product_name);
-- Composite index for common query patterns
CREATE INDEX idx_product_date ON sales(product_name, sale_date);
With proper indexes, this query uses an index scan instead of a full table scan:
EXPLAIN SELECT
product_name,
SUM(quantity) AS total_quantity
FROM sales
WHERE sale_date >= '2024-01-01'
GROUP BY product_name;
Check the EXPLAIN output. You want to see “Using index” or “Using index condition,” not “Using filesort” or full table scans.
Avoid calculating SUM on expressions when possible. This query can’t use an index on price:
-- Slower: calculation prevents index usage
SELECT SUM(price * 1.1) FROM sales;
-- Faster: calculate after aggregation
SELECT SUM(price) * 1.1 FROM sales;
For frequently accessed totals, consider materialized views or summary tables:
CREATE TABLE daily_sales_summary (
summary_date DATE PRIMARY KEY,
total_quantity INT,
total_revenue DECIMAL(12,2),
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
-- Update via scheduled job or trigger
INSERT INTO daily_sales_summary (summary_date, total_quantity, total_revenue)
SELECT
sale_date,
SUM(quantity),
SUM(price * quantity)
FROM sales
WHERE sale_date = CURDATE()
GROUP BY sale_date
ON DUPLICATE KEY UPDATE
total_quantity = VALUES(total_quantity),
total_revenue = VALUES(total_revenue),
last_updated = CURRENT_TIMESTAMP;
This trades storage space for query speed—perfect for dashboards that display the same totals repeatedly.
Always validate your SUM results against business logic. Test with known datasets, verify NULL handling, and ensure your GROUP BY includes all non-aggregated columns in your SELECT list (unless you’re using MySQL’s non-standard behavior with sql_mode disabled, which you shouldn’t).
The SUM function is straightforward but powerful. Master these patterns and you’ll handle most aggregation requirements efficiently.