SUM()

SUM() Function in MySQL

Introduction
Total calculations across rows are essential for financial and analytical reporting.
SUM() function adds numeric values within each group or entire result set.
It is widely used for:

• Sales totals
• Score aggregation
• Financial summaries

What is SUM() Function
SUM() returns total of non-NULL numeric values in specified column.
Ignores NULL values automatically.

Basic Syntax

sql

SELECT SUM(column_name) FROM table_name; 

Example

sql

SELECT SUM(marks) as total_marks FROM Students; 

Returns sum of all student marks.

SUM with GROUP BY
Most common pattern.

sql

SELECT city, SUM(marks) as city_total 

FROM Students 

GROUP BY city; 

SUM with WHERE
Conditional totals.

sql

SELECT SUM(marks) FROM Students 

WHERE status = 'Active'; 

SUM with Multiple Aggregates
Combine with other functions.

sql

SELECT city, 

       SUM(marks) as total_marks, 

       COUNT(*) as student_count, 

       AVG(marks) as avg_marks 

FROM Students 

GROUP BY city; 

SUM with ORDER BY
Sort aggregated totals.

sql

SELECT department, SUM(marks) as dept_total 

FROM Students 

GROUP BY department 

ORDER BY dept_total DESC; 

SUM with JOIN
Aggregate across tables.

sql

SELECT c.course_name, SUM(s.marks) as course_total 

FROM Students s 

JOIN Courses c ON s.course_id = c.id 

GROUP BY c.id, c.course_name; 

SUM with HAVING
Filter grouped sums.

sql

SELECT city, SUM(marks) as total 

FROM Students 

GROUP BY city 

HAVING SUM(marks) > 1000; 

Conditional SUM with CASE
Multiple conditions within SUM.

sql

SELECT city,

       SUM(CASE WHEN marks > 90 THEN marks ELSE 0 END) as high_scores,

       SUM(CASE WHEN marks BETWEEN 70 AND 89 THEN marks ELSE 0 END) as good_scores

FROM Students

GROUP BY city;

SUM with Subquery
Nested total calculations.

sql

SELECT city, SUM(marks) as city_total,

       (SELECT SUM(marks) FROM Students WHERE status = 'Active') as grand_total

FROM Students

GROUP BY city;

SUM DISTINCT
Sum unique values only.

sql

SELECT SUM(DISTINCT marks) as unique_marks_sum 

FROM Students; 

Handling NULLs
SUM ignores NULLs by design.

sql

SELECT SUM(marks) FROM Students;  -- NULL marks skipped 

SELECT COALESCE(SUM(marks), 0) as total;  -- 0 if all NULL 

Execution Order with SUM
Applied after FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT

Performance Considerations

• Index GROUP BY columns
• SUM(*) invalid (use numeric column)
• Large tables: consider approximate counts
• SUM(DISTINCT) slower due to deduplication

Important Notes

• Ignores NULL values automatically
• Empty result = NULL (use COALESCE)
• Works only on numeric columns
• SUM(DISTINCT) removes duplicates first
• Compatible with GROUP BY, HAVING

Example Scenario
Complete sales performance report:

sql

SELECT

    s.name as salesperson,

    COUNT(o.id) as total_orders,

    SUM(o.amount) as total_sales,

    AVG(o.amount) as avg_order_value,

    SUM(CASE WHEN o.status = 'Completed' THEN o.amount ELSE 0 END) as confirmed_sales

FROM Salespeople s

LEFT JOIN Orders o ON s.id = o.salesperson_id

GROUP BY s.id, s.name

HAVING SUM(o.amount) > 50000

ORDER BY total_sales DESC;

Common Mistakes

• SUM on non-numeric columns
• Forgetting COALESCE for NULL results
• WHERE vs HAVING confusion
• Performance hit from SUM(DISTINCT)
• GROUP BY without aggregate functions

Key Points to Remember

• SUM adds non-NULL numeric values
• GROUP BY + SUM = category totals
• CASE WHEN inside SUM for conditions
• Ignores NULLs automatically
• HAVING filters SUM results
• COALESCE(SUM(), 0) for empty results