SQL

SUM() in SQL

➕ The SUM() function is an aggregate function in SQL that calculates the total of numeric values in a column. It is widely used in reports, financial calculations, sales analysis, inventory management, and statistical summaries. The SUM()function works only with numeric data types and ignores NULL values.

📖 What is SUM()?

The SUM() function adds together all numeric values in a specified column and returns the total. It can be used on an entire table or on filtered groups of records.

Information

SUM() works with numeric data types such as INT, DECIMAL, FLOAT, and DOUBLE. It ignores NULL values during calculation.

📝 Basic Syntax

SUM() Syntax

SELECT SUM(column_name)
FROM table_name
WHERE condition;

📊 Sample Table

Assume the following Students table:

StudentID	Name	Department	Marks	FeesPaid
101	Alice	Computer Science	92	50000
102	Bob	Mathematics	85	45000
103	Charlie	Physics	78	40000
104	David	Computer Science	95	50000
105	Eva	Mathematics	88	47000

1️⃣ Calculating the Total of a Column

Calculate the total fees paid by all students.

Total Fees Paid

SELECT SUM(FeesPaid) AS TotalFees
FROM Students;

Result:

TotalFees
232000

2️⃣ SUM() with WHERE

Use the WHERE clause to calculate totals for selected rows only.

Total Fees for Computer Science Students

SELECT SUM(FeesPaid) AS TotalCSFees
FROM Students
WHERE Department = 'Computer Science';

3️⃣ SUM() with DISTINCT

The DISTINCT keyword allows you to sum only unique values in a column.

SUM of Distinct Fees

SELECT SUM(DISTINCT FeesPaid) AS UniqueFeeTotal
FROM Students;

Tip

SUM(DISTINCT Column) ignores duplicate values before performing the calculation.

📊 SUM() with GROUP BY

The GROUP BY clause groups records, and SUM() calculates the total for each group.

Total Fees by Department

SELECT Department,
       SUM(FeesPaid) AS TotalFees
FROM Students
GROUP BY Department;

Sample Result:

Department	TotalFees
Computer Science	100000
Mathematics	92000
Physics	40000

📈 SUM() with HAVING

The HAVING clause filters grouped results after totals have been calculated.

Departments with Total Fees Above 90000

SELECT Department,
       SUM(FeesPaid) AS TotalFees
FROM Students
GROUP BY Department
HAVING SUM(FeesPaid) > 90000;

🔗 SUM() with JOIN

The SUM() function is frequently used with joins to calculate totals across related tables.

Total Course Fees per Student

SELECT s.Name,
       SUM(c.CourseFee) AS TotalCourseFee
FROM Students s
JOIN Courses c
ON s.StudentID = c.StudentID
GROUP BY s.Name;

🧮 SUM() with Expressions

You can use arithmetic expressions inside the SUM() function.

Total Marks with Bonus

SELECT SUM(Marks + 5) AS TotalBonusMarks
FROM Students;

⚠️ SUM() and NULL Values

The SUM() function automatically ignores NULL values. Only valid numeric values are included in the total.

Column Values	SUM() Result
10, 20, NULL, 30	60

Important

If every value in the selected column is NULL, SUM() returns NULL, not zero.

📊 Common Uses of SUM()

Purpose	Example
Total Sales	SUM(SalesAmount)
Total Salary	SUM(Salary)
Total Marks	SUM(Marks)
Total Fees	SUM(FeesPaid)
Department-wise Totals	SUM(...) GROUP BY Department

💼 Real-World Example

A university administrator wants to calculate the total fees collected from each department.

Department-wise Fee Collection

SELECT Department,
       SUM(FeesPaid) AS TotalCollection
FROM Students
GROUP BY Department
ORDER BY TotalCollection DESC;

This query generates a summary report showing the total fee collection for every department.

⚠️ Best Practices

Best Practice

Use SUM() only with numeric columns, combine it with WHERE to calculate filtered totals, use GROUP BY for grouped summaries, and remember that SUM() ignores NULL values. Use COALESCE() if you need to replace a NULL result with a default value such as 0.

🚀 Key Points to Remember

📌 SUM() calculates the total of numeric values.
📌 It ignores NULL values.
📌 SUM(DISTINCT Column) adds only unique values.
📌 It is commonly used with WHERE, GROUP BY, and HAVING.
📌 SUM() can be combined with expressions and joins.
📌 It is widely used for financial reports, analytics, and business intelligence.

>>"The SUM() function transforms individual numbers into meaningful totals, making data analysis simple and effective."

Summary

✅ The SUM() function is one of the most important SQL aggregate functions for calculating totals. Whether summing sales, salaries, marks, or fees, it provides accurate summaries that support reporting, analytics, and decision-making across a wide range of applications.