MySQL SUM() Function

MySQL SUM() Function: Calculating Numeric Totals

The MySQL SUM() aggregate function adds all non-NULL numeric values in a column or expression and returns the total. Developers rely on SUM() for order revenue, invoice balances, inventory counts, and dashboard KPIs.

Basic Syntax

SELECT SUM(numeric_expression) AS total_value
FROM table_name
WHERE condition;

If every row in the filtered result is NULL or zero rows match, SUM() returns NULL rather than 0. Always handle empty aggregates explicitly in reporting queries so totals remain numeric.

Practical SQL Examples

This query sums paid revenue per customer in demo_orders and converts missing totals to zero safely:

SELECT
    customer_id,
    COUNT(*) AS order_count,
    COALESCE(SUM(total_amount), 0.00) AS total_spent
FROM demo_orders
WHERE status = 'paid'
GROUP BY customer_id
ORDER BY total_spent DESC;

You can also run multi-status pivot totals in a single query using SUM(CASE WHEN ...):

SELECT
    SUM(CASE WHEN status = 'paid' THEN total_amount ELSE 0 END) AS paid_revenue,
    SUM(CASE WHEN status = 'refunded' THEN total_amount ELSE 0 END) AS refunded_amount
FROM demo_orders;

Common Mistakes and Best Practices

• Wrap SUM() in COALESCE(): Always write COALESCE(SUM(total_amount), 0) so APIs and math expressions never receive an unexpected NULL on empty sets.
• Avoid SUM() After One-to-Many Joins: Joining demo_orders to demo_order_items duplicates demo_orders.total_amount once per item. Sum the child column (demo_order_items.line_total) or aggregate inside a subquery before joining.
• Use DECIMAL for Money: Store prices in DECIMAL(10, 2) instead of FLOAT or DOUBLE so SUM() returns exact cents without binary rounding drift.

Frequently Asked Questions

What happens if SUM() runs on a VARCHAR column?

MySQL attempts implicit numeric conversion, truncating non-numeric suffixes, which often hides data errors; always store numbers in numeric types.

Does SUM() ignore NULL values?

Yes, SUM() skips NULL cells automatically and sums only non-NULL numbers.

How do I filter groups whose sum exceeds a threshold?

Use HAVING SUM(total_amount) > 500 after GROUP BY, because WHERE runs before grouping.