SQL SUM() Function

In every data-driven organization, there is often a need to aggregate values. This often involves calculating the total revenue from sales, the number of hours worked by an employee, or the combined scores of all students in a class. Aggregating such data is essential for analyzing performance, identifying trends, and making informed business decisions. To handle such calculations efficiently, SQL provides the SUM() function. It computes the total of a numeric column. It is an essential tool for building reports and dashboards. In this blog, we will learn about the SQL SUM() function in detail.

Table of Contents:

• Understanding the SQL SUM() Function
  • Syntax and Example of SUM() in SQL
  • Using the SUM() Function with the WHERE Clause in SQL
  • Using the SUM() Function with the GROUP BY Clause in SQL
  • Using the SUM() Function with an Expression in SQL
  • Using the SUM() Function in Subqueries in SQL
• Real-world Example
• Common Mistakes to Avoid
• Best Practices
• Conclusion

Understanding 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 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 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

Syntax and Example of SUM() in SQL

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, by adding up the costs, the SUM(cost) function determines the total cost and returns a single aggregated value.

Using the SUM() Function with the WHERE Clause in SQL

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 WHERE clause filters the cities with Kochi or Mysuru, and the SUM() function adds only the filtered values. Thus, the total cost is returned as 1950.00.

Using the SUM() Function with the GROUP BY Clause in SQL

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 query groups the rows by city and uses the SUM() function. After grouping, there will be one row per city showing the total maintenance cost for that city. 

Using the SUM() Function with an Expression in SQL

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 expression cost * 1.18 calculates the 18% tax added to each cost. The SUM() function adds together all of the taxed amounts.

Using the SUM() Function in Subqueries in SQL

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 subquery calculates the average cost, and the outer query fetches technicians whose individual ticket cost is greater than the average. Karthik’s ticket cost exceeds the average, so his record is included in the output.

Real-world Example

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 Mistakes to Avoid

• Using the SUM() function on columns apart from numeric columns
  • Incorrect: SELECT SUM(student_name) FROM students;
  • Fix: Use SUM() 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 automatically ignores NULL values, which can lead to misleading totals if you assume NULLs are treated as 0.

Best Practices

• 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 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