← Back to Chapters

MySQL SUM() Function

? MySQL SUM()

? Quick Overview

The SUM() function in MySQL calculates the total (sum) of a numeric column. It's commonly used with GROUP BY to aggregate values per group, or alone to get a table-wide total.

? Key Concepts

  • Purpose: Add up numeric column values.
  • NULLs: SUM() ignores NULL values.
  • Use with GROUP BY: Combine with GROUP BY to get totals per group.
  • Non-numeric columns: MySQL will attempt to cast; avoid relying on implicit casts.

? Syntax / Theory

Basic forms:

  • SELECT SUM(column) FROM table; — total over whole table.
  • SELECT group_col, SUM(value_col) FROM table GROUP BY group_col; — totals per group.

? Code Example — Simple SUM

? View Code Example 
-- Create a simple employees table
CREATE TABLE Employees (
id INT PRIMARY KEY,
name VARCHAR(50),
salary DECIMAL(10,2)
);
-- Insert some sample data
INSERT INTO Employees (id, name, salary) VALUES
(1, 'Asha', 50000.00),
(2, 'Ravi', 60000.00),
(3, 'Maya', NULL),
(4, 'Karan', 72000.00);
-- Calculate total payroll (NULL ignored)
SELECT SUM(salary) AS total_payroll FROM Employees;

Live Output (example)

total_payroll
-------------
182000.00

Explanation: The NULL salary for Maya is ignored; only numeric values are summed.

? Code Example — SUM with GROUP BY

? View Code Example 
-- Create Orders table with amount and category
CREATE TABLE Orders (
order_id INT PRIMARY KEY,
category VARCHAR(30),
amount DECIMAL(10,2)
);
-- Insert sample orders
INSERT INTO Orders (order_id, category, amount) VALUES
(101, 'books', 120.50),
(102, 'books', 45.00),
(103, 'electronics', 399.99),
(104, 'books', 15.00),
(105, 'electronics', 200.00);
-- Get total sales per category
SELECT category, SUM(amount) AS total_sales
FROM Orders
GROUP BY category
ORDER BY total_sales DESC;

Live Output (example)

category      total_sales
-------------------------
electronics   599.99
books         180.50

Explanation: Grouping by category gives per-category totals.

? Code Example — DISTINCT & HAVING

? View Code Example 
-- Suppose a payments table with repeated amounts
CREATE TABLE Payments (
payment_id INT PRIMARY KEY,
user_id INT,
amount DECIMAL(8,2)
);
-- Insert sample payments
INSERT INTO Payments (payment_id, user_id, amount) VALUES
(1, 10, 50.00),
(2, 10, 50.00),
(3, 11, 75.00),
(4, 12, 20.00);
-- Sum of distinct amounts per user (demonstration)
SELECT user_id, SUM(DISTINCT amount) AS sum_distinct
FROM Payments
GROUP BY user_id;
-- Using HAVING to filter groups with totals > 50
SELECT user_id, SUM(amount) AS total_paid
FROM Payments
GROUP BY user_id
HAVING SUM(amount) > 50;

Notes

  • SUM(DISTINCT ...) removes duplicate values before summing.
  • HAVING filters on aggregated results (can't use WHERE for aggregates).

? Use Cases

  • Financial reports (total revenue, payroll totals).
  • Analytics dashboards (sales by product, region).
  • Data validation checks (compare expected vs actual totals).

? Tips & Best Practices

  • Ensure the column you SUM is numeric (use appropriate data types like DECIMAL for money).
  • Remember that SUM() ignores NULL; if you want to treat NULL as zero, use COALESCE(col,0).
  • Use GROUP BY when you need per-group aggregates and HAVING to filter aggregate results.
  • When summing large datasets, consider indexes on grouping columns to improve performance.

? Try It Yourself / Practice Tasks

  1. Create a sales table with region, product, and amount. Compute total sales per region.
  2. Use COALESCE to treat missing amounts as zero and compute overall total.
  3. Find top 3 categories by total sales.