How to Use the SUM Function in SQL?

Welcome back to our SQL tutorial series! In this article, we’re going to explore the SUM function, which is used to calculate the total sum of a numeric column. Understanding how to use the SUM function is essential for data analysis, allowing you to quickly aggregate and analyze your numerical data.

Understanding the SUM Function

The SUM function calculates the total sum of a numeric column. It can be used to sum values in a single column, and it works particularly well with columns that store financial, sales, or other quantitative data.

Basic Syntax of SUM

The basic syntax of the SUM function is as follows:

SELECT SUM(column_name) FROM table_name WHERE condition;

Let’s look at some practical examples to understand how to use the SUM function effectively.

Example Table: employees

We’ll continue to use the employees table for our examples:

Calculating the Total Sum of a Column

To calculate the total sum of salaries in the employees table, you use:

SELECT SUM(salary) AS total_salaries FROM employees;

This command returns:

The total sum of salaries in the employees table is $375,000.00.

Using SUM with WHERE Clause

You can use the SUM function with the WHERE clause to filter the results based on specific conditions. For example, to calculate the total sum of salaries for Software Engineers:

SELECT SUM(salary) AS total_software_engineer_salaries FROM employees WHERE position = 'Software Engineer';

This command returns:

The total sum of salaries for Software Engineers is $155,000.00.

Using SUM with GROUP BY

You can use the SUM function with the GROUP BY clause to calculate the sum for each group. For example, to calculate the total sum of salaries for each position:

SELECT position, SUM(salary) AS total_salaries
FROM employees
GROUP BY position;

This command returns:

Using SUM with HAVING

You can use the SUM function with the HAVING clause to filter groups based on a condition. For example, to find positions with a total salary greater than $100,000:

SELECT position, SUM(salary) AS total_salaries
FROM employees
GROUP BY position
HAVING SUM(salary) > 100000;

This command returns:

Using SUM with Multiple Columns

You can also use the SUM function with multiple columns to calculate the sum of expressions. For example, if you want to calculate the total compensation (salary + bonus) for employees, assuming there’s a bonus column:

SELECT SUM(salary + bonus) AS total_compensation FROM employees;

If the bonus column has the following values:

Then this command would return:

Using SUM with JOINs

You can also use the SUM function in conjunction with JOINs to calculate totals across related tables. For example, if you have another table departments:

And the employees table has a department_id column:

To calculate the total sum of salaries for each department, you use:

SELECT d.department_name, SUM(e.salary) AS total_salaries
FROM employees e
JOIN departments d ON e.department_id = d.department_id
GROUP BY d.department_name;

This command returns:

Conclusion

The SUM function is a powerful tool for aggregating numeric data in your SQL database. Whether you’re calculating total salaries, sales, or other quantitative measures, understanding how to use the SUM function is essential for effective data analysis and reporting.

In our next tutorial, we’ll explore more advanced SQL topics and commands. Stay tuned and happy coding!