MySQL sum() Function

Last updated: October 19, 2026Oct 19, 2026

SUM() adds up the values in a column: total revenue, total hours, total stock. Together with GROUP BY, conditions and window functions it answers most "how much?" questions.

1. Basic SUM

Table: bills (6 rows)

SELECT SUM(amount) AS revenue, SUM(items) AS items_sold, SUM(tip) AS tips FROM bills;

Result: 1 row

Tips include NULLs, which SUM simply skips.

2. SUM with WHERE and GROUP BY

SELECT SUM(amount) AS aundh_revenue FROM bills WHERE outlet = 'Aundh';

Result: 1 row

SELECT outlet, SUM(amount) AS revenue, SUM(items) AS items FROM bills GROUP BY outlet ORDER BY revenue DESC;

Result: 2 rows

SELECT waiter, SUM(amount) AS sales FROM bills GROUP BY waiter HAVING SUM(amount) > 1000;

Result: 2 rows

3. NULLs and Empty Results

SUM of no rows (or only NULLs) is NULL, not 0. Wrap it in COALESCE for reports:

SELECT SUM(amount) AS raw_sum, COALESCE(SUM(amount), 0) AS safe_sum FROM bills WHERE outlet = 'Baner';

Result: 1 row

SELECT SUM(tip) AS tips_bina FROM bills WHERE waiter = 'Bina';   -- only NULL tips

Result: 1 row

4. Summing Expressions

SELECT SUM(amount + COALESCE(tip, 0)) AS amount_with_tips, SUM(amount * 0.05) AS gst_5pct FROM bills;

Result: 1 row

SELECT SUM(amount + tip) AS wrong, SUM(amount + COALESCE(tip, 0)) AS right_total FROM bills;

Result: 1 row

5. Conditional Sums (Pivot)

Sum only the rows that match a condition, several at once. This turns rows into columns (a pivot):

SELECT outlet,
       SUM(CASE WHEN payment = 'cash' THEN amount ELSE 0 END) AS cash,
       SUM(CASE WHEN payment = 'card' THEN amount ELSE 0 END) AS card,
       SUM(CASE WHEN payment = 'upi'  THEN amount ELSE 0 END) AS upi,
       SUM(amount) AS total
FROM bills
GROUP BY outlet;

Result: 2 rows

A shorter MySQL form: SUM(IF(payment = 'cash', amount, 0)).

6. SUM(DISTINCT)

SUM(DISTINCT col) adds each different value once. It's rarely what you want for money (two bills of ₹900 are both real), so use it with care:

SELECT SUM(items) AS items_total, SUM(DISTINCT items) AS distinct_item_counts FROM bills;

Result: 1 row

7. Running Totals with SUM() OVER

SELECT bill_date, outlet, amount,
       SUM(amount) OVER (ORDER BY bill_date, bill_id)                      AS running_total,
       SUM(amount) OVER (PARTITION BY outlet ORDER BY bill_date, bill_id)  AS outlet_running,
       ROUND(100 * amount / SUM(amount) OVER (), 1)                        AS pct_of_all
FROM bills
ORDER BY bill_date, bill_id;

Result: 6 rows

8. Result Types and Precision

• SUM of integers or DECIMAL returns DECIMAL, so totals stay exact.
• SUM of FLOAT/DOUBLE returns DOUBLE and may show tiny rounding errors; store money as DECIMAL.
• If a DECIMAL total exceeds 65 digits, MySQL raises an out-of-range error.

SELECT 0.1 + 0.2 AS decimal_math, CAST(0.1 AS DOUBLE) + CAST(0.2 AS DOUBLE) AS double_math;

Result: 1 row

9. Practice Exercises

SELECT bill_date, SUM(amount) AS revenue, COALESCE(SUM(tip), 0) AS tips
FROM bills GROUP BY bill_date ORDER BY bill_date;

SELECT waiter, SUM(amount) AS sales,
       ROUND(100 * SUM(amount) / (SELECT SUM(amount) FROM bills), 1) AS pct
FROM bills GROUP BY waiter ORDER BY sales DESC;

10. Summary

• SUM(col) adds up non-NULL values; SUM of nothing is NULL, so use COALESCE.
• Combine with WHERE, GROUP BY and HAVING for filtered and grouped totals.
• Watch NULLs inside expressions; COALESCE them first.
• SUM(CASE …) builds pivots; SUM() OVER (ORDER BY …) builds running totals.
• Use DECIMAL for exact money totals.