SQL SUM() Function

In data-driven environments, analyzing metrics like total sales, revenue, or scores is essential for strategic decision-making. SQL provides powerful aggregate functions, which compute a single result from a set of values, for this purpose. One of the most widely used is the SQL SUM() function, ideal for calculating totals such as overall cost, revenue, or hours. It plays a key role in reporting, from a sum query in SQL to a cumulative sum in SQL, and even more advanced use cases like rolling sum in SQL. Whether you’re working with SUM() in SQL Server, applying it with a GROUP BY clause, or filtering with a WHERE clause, understanding this function is fundamental to SQL analytics. In this blog, we’ll explore the SUM() function in SQL in depth with examples and practical tips.

Table of Contents:

• What is the SQL SUM() Function?
  • SQL SUM() Function Syntax and Examples
  • How to Use SQL SUM() with WHERE Clause?
  • How to Use SQL SUM() with GROUP BY?
  • How to Use SQL SUM() with Arithmetic Expressions?
  • How to Use SQL SUM() in Subqueries?
• SQL SUM() vs COUNT(): What’s the Difference?
• Alternatives to SUM() for Advanced SQL Aggregation
• Real-World Use Case of SQL SUM() Function in SQL Server
• Common Errors While Using SQL SUM() Function
• Handling NULL Values in SQL SUM(): Tips & Tricks
• Best Practices When Using SUM() in SQL
• Conclusion

What is the SQL SUM() Function?

The SUM() function in SQL is used to add up the total value of a numeric column, regardless of how many rows you have. SUM() is an aggregate function that performs a computation on a set of values and returns a single result. SUM() is commonly used for summarizing values, such as total sales, total expenses, or total hours worked across multiple rows in the whole table, or in some groups with the GROUP BY clause. The SQL SUM() function will only work with numeric data types, and you can use filtering conditions like WHERE or grouping conditions to extract meaningful insights.

Before moving forward with the SQL SUM() function, we need to create a table for Device Maintenance.

CREATE TABLE dvc_mnt (
    tckt_id INT PRIMARY KEY,
    dvc_owner VARCHAR(60),
    city VARCHAR(50),
    dvc_type VARCHAR(40),
    issue VARCHAR(100),
    cost DECIMAL(10,2),
    tch_name VARCHAR(50)
);

INSERT INTO dvc_mnt (
    tckt_id, dvc_owner, city, dvc_type, issue, cost, tch_name
) VALUES
(101, 'Sanjay', 'Mysuru', 'Router', 'Frequent disconnections', 750.00, 'Manoj'),
(102, 'Ritika', 'Kochi', 'Smartphone', 'Battery overheating', 1200.00, 'Neeraj'),
(103, 'Aravind', 'Jodhpur', 'Printer', 'Paper jam issue', 950.00, 'Sowmya'),
(104, 'Ishita', 'Trichy', 'Laptop', 'Screen flickering', 3000.00, 'Karthik');

SELECT * from dvc_mnt;

This is how the table looks once it is created and inserted with values.

Learn SQL from Scratch: Build a Rock-Solid Data Foundation

Unlock the power of databases with hands-on SQL training and become a data-driven professional

Explore Program

SQL SUM() Function Syntax and Examples

Syntax:

SELECT SUM(cl) 
FROM tab_name;

Example:

-- To find the total costs
SELECT SUM(cost) AS total_costs 
FROM dvc_mnt;

Output:

Explanation: Here, the cumulative sum in SQL by adding up the costs, the SUM(cost) function determines the total cost and returns a single aggregated value.

How to Use SQL SUM() with WHERE Clause?

The SUM() function can be combined with the WHERE clause to compute the sum of a numeric column, only for those rows that meet specific criteria.

Syntax:

SELECT SUM(column_name) AS total_alias
FROM table_name
WHERE condition;

Example:

-- To calculate the total maintenance cost in the cities of Kochi or Mysuru  
SELECT SUM(cost) AS total_cost
FROM dvc_mnt
WHERE city IN ('Kochi', 'Mysuru');

Output:

Explanation: Here, the SQL SUM() with a WHERE clause filters the running sum in SQL cities with Kochi or Mysuru, and the sum query in SQL adds only the filtered values. Thus, the Rolling sum in SQL is returned as 1950.00.

How to Use SQL SUM() with GROUP BY?

The SUM() function can be used with the GROUP BY clause to generate separate totals for each group of values in a column. 

Syntax:

SELECT group_column, SUM(numeric_column) AS total_alias
FROM table_name
GROUP BY group_column;

Example:

SELECT city, SUM(cost) AS total_cost
FROM dvc_mnt
GROUP BY city;

Output:

Explanation: Here, the SUM() query in SQL groups the rows by city and uses the SUM() in SQL with GROUP BY. After grouping, there will be one row per city showing the total maintenance cost for that city. 

How to Use SQL SUM() with Arithmetic Expressions?

The SUM() function can also be used with an arithmetic expression to calculate the cost, including tax.

Syntax:

SELECT SUM(expression) AS total_alias
FROM table_name
WHERE condition;

Example:

-- 18% tax included
SELECT SUM(cost * 1.18) AS total_with_tax   
FROM dvc_mnt;

Output:

Explanation: Here, the sum of two columns in SQL, the expression cost * 1.18 calculates the 18% tax added to each cost. The sum query in SQL adds together all of the taxed amounts.

How to Use SQL SUM() in Subqueries?

The SUM() function can be used in subqueries to calculate the totals that can be filtered or joined with the outer queries.

Syntax:

SELECT column1, ...
FROM table_name
WHERE some_column > (
    SELECT SUM(column_name)
    FROM another_table
    WHERE condition
);

Example:

SELECT tch_name, cost
FROM dvc_mnt
WHERE cost > (
    SELECT SUM(cost) / COUNT(*) FROM dvc_mnt
);

Output:

Explanation: The SQL sum column in the subquery calculates the average cost, and the outer query fetches technicians whose individual ticket cost is greater than the average. The sum of the two columns in SQL is displayed, with Karthik’s ticket cost exceeds the average, so his record is included in the output.

SQL SUM() vs COUNT(): What’s the Difference?

Understanding the difference between SUM() and COUNT() is essential when working with aggregate functions in SQL.

Alternatives to SUM() for Advanced SQL Aggregation

While SUM() is effective for basic aggregation, advanced reporting may require additional techniques or functions.

1. Window Functions for Running Totals:
Use SUM() as a window function to compute a cumulative sum in SQL.

Example for Running Total (Cumulative Sum in SQL / Running Sum in SQL):

CREATE TABLE dvc_mnt (
    tckt_id INT PRIMARY KEY,
    dvc_owner VARCHAR(60),
    city VARCHAR(50),
    dvc_type VARCHAR(40),
    issue VARCHAR(100),
    cost DECIMAL(10,2),
    tax DECIMAL(10,2),
    tch_name VARCHAR(50)
);
INSERT INTO dvc_mnt (
    tckt_id, dvc_owner, city, dvc_type, issue, cost, tax, tch_name
) VALUES
(101, 'Sanjay', 'Mysuru', 'Router', 'Frequent disconnections', 750.00, 135.00, 'Manoj'),
(102, 'Ritika', 'Kochi', 'Smartphone', 'Battery overheating', 1200.00, 216.00, 'Neeraj'),
(103, 'Aravind', 'Jodhpur', 'Printer', 'Paper jam issue', 950.00, 171.00, 'Sowmya'),
(104, 'Ishita', 'Trichy', 'Laptop', 'Screen flickering', 3000.00, 540.00, 'Karthik'),
(105, 'Nikhil', 'Kochi', 'Modem', 'No internet', 600.00, 108.00, 'Manoj'),
(106, 'Diya', 'Mysuru', 'Scanner', 'Poor resolution', 800.00, 144.00, 'Sowmya'),
(107, 'Aditya', 'Trichy', 'Tablet', 'Touchscreen lag', 1600.00, 288.00, 'Neeraj');

SELECT tckt_id, cost, SUM(cost) OVER (ORDER BY tckt_id) AS running_total
FROM dvc_mnt;

Output:

Explanation: The running sum in SQL gives row-by-row accumulation of cost values, ordered by tckt_id.

2. Derived Columns for Arithmetic Aggregation:
For combining values across columns:

Example for Sum of Two Columns in SQL (cost + tax):

SELECT SUM(cost + tax) AS total_cost
FROM dvc_mnt;

Output:

Explanation: The sum query in SQL returns the total maintenance cost, including tax.

3. In SQL Server (SUM() Function in SQL Server):
Advanced analytics in SQL Server often combines SUM() with PARTITION BY for grouped running totals.

Example for SUM() in SQL Server with PARTITION BY (Rolling Sum Per Technician):

SELECT 
  tch_name, tckt_id, cost,
  SUM(cost) OVER (PARTITION BY tch_name ORDER BY tckt_id) AS rolling_sum_by_technician
FROM dvc_mnt;

Output:

Explanation: Rolling sum in SQL for SQL Server, this calculates a rolling sum per tch_name, ordered by ticket.

These alternatives help when SQL SUM() needs to be extended for more complex analytical tasks beyond a simple sum query in SQL.

Real-World Use Case of SQL SUM() Function in SQL Server

Technical Institutes: Consider a technical institute like Intellipaat, where the project scores of the students have to be analyzed.

Example:

CREATE TABLE project_scores (
    prj_id INT PRIMARY KEY,
    student_name VARCHAR(100),
    course VARCHAR(50),
    project_score DECIMAL(4,2)
);

INSERT INTO project_scores (prj_id, student_name, course, project_score) VALUES
(201, 'Nikhil', 'Data Science', 87.50),
(202, 'Ritika', 'AI & ML', 91.00),
(203, 'Arjun', 'Cyber Security', 84.25),
(204, 'Kavya', 'AI & ML', 90.50),
(205, 'Rohan', 'UI/UX Design', 78.00),
(206, 'Diya', 'Cyber Security', 86.30),
(207, 'Varun', 'Data Science', 88.10),
(208, 'Sneha', 'UI/UX Design', 79.60),
(209, 'Aditya', 'AI & ML', 92.40);

SELECT 
    SUM(project_score) AS total_project_score
FROM project_scores;

Output:

Explanation: Here, the SUM(project_score) function calculates the overall project performance.

Common Errors While Using SQL SUM() Function

• Using the SUM() function on columns apart from numeric columns
  • Incorrect: SELECT SUM(student_name) FROM students;
  • Fix: Use a sum query in SQL only on numeric fields such as GPA, cost, salary, etc.

• Forgetting the GROUP BY Clause
  • Incorrect: SELECT department, SUM(gpa) FROM students;
  • Fix: Include GROUP BY department to aggregate properly by group.

• Misusing WHERE Instead of HAVING
  • Incorrect: WHERE SUM(gpa) > 10
  • Fix: Use HAVING SUM(GPA) > 10 to filter based on aggregate results.

• Ignoring NULL values
  • SUM() Function in SQL Server automatically ignores NULL values, which can lead to misleading totals if you assume NULLs are treated as 0.

Handling NULL Values in SQL SUM(): Tips & Tricks

By default, the SQL SUM() function ignores NULL values, which can lead to underestimating totals in cases where missing values are expected.

Tips to Handle NULLs Effectively:

1. Avoid surprises in rolling or running sums
In scenarios such as calculating Running sum in SQL or rolling sum in SQL, NULLs can silently exclude values, affecting cumulative totals.

2. Use COALESCE() to replace NULL with 0

SELECT SUM(COALESCE(cost, 0)) FROM dvc_mnt;

3. Ensure correct assumptions
Don’t assume NULL means zero unless explicitly defined.

4. Combine with GROUP BY safely
When using SUM() in SQL with GROUP BY, be sure all grouped values are non-null or accounted for with COALESCE().

Best Practices When Using SUM() in SQL

• Always Verify Data Types: Utilize SUM() only on numeric data types, like INT, DECIMAL, FLOAT, etc.
• Use GROUP BY, HAVING, and WHERE clauses efficiently: Use WHERE to reduce the number of rows before aggregation. Use HAVING to filter the aggregated results.
• Handle NULL values correctly: To avoid the occurrence of accidental NULL values in the report analysis, consider using COALESCE() or ISNULL()
• Use proper aliases for the query: 

SELECT department, SUM(GPA) as total_gpa
FROM students
GROUP BY department; 

SQL Simplified: Learn for Free, Grow for Life

Master the language of data and start your journey toward a data-centric career—without spending a dime!

Explore Program

Conclusion

The SQL SUM() function is an important and valuable aggregate function for aggregating numeric data. This can be used in scenarios like analysing the student report, calculating the revenues, or aggregating the values in groups. You can use the SUM() Function in SQL Server with WHERE, GROUP BY, HAVING, and even with expressions or subqueries. When combined, these options allow SUM() to handle a wide range of real-world analytical scenarios. Avoiding the common mistakes and following the best practices makes the query clean and more reliable. In this blog, you have gained knowledge of the SQL SUM() function in detail.

Take your skills to the next level by enrolling in the SQL Training Course today and gaining hands-on experience. Also, prepare for job interviews with SQL interview questions, prepared by industry experts.

SQL SUM() Function – FAQs