How to Use SQL SUM Function

SQL is a powerful and widely-used programming language for data storage, retrieval, and manipulation. One of the most commonly-used operators in SQL is SUM – a logical operation that produces the sum of all values in a column. This article will cover the basics of this operator usage in queries.

Why Do You Need SQL SUM?

You can use the SUM operator to calculate the total cost of all items purchased. Or, you can apply it to find the sum of all grades on an exam or the total number of people who attended a concert. These are just some examples. In SQL, the SUM function can be a part of a query that will produce multiple results, like in the SELECT statement that produces order totals.

It may seem boring, but it totally depends on what you’re using it for. For example, you have the people’s salary details and need to know how much money was spent on a particular item. Or, you need to define someone who had spent more than $50,000,000 during a specific period.

This article will show you the SUM operator’s function and how it can be useful for you.

What is the SUM Operator in SQL Exactly?

The SUM operator in SQL adds up the values of all the columns specified. Thus, it will sum the values and then output a single number for every column you specify.

Summing all values in one column (or table) is, probably, one of the most common and well-known operations in SQL.

The systems that support the SUM operator are MySQL, PostgreSQL, SQL Server, and Oracle. These all are the top systems.

How to Use the SQL SUM Function?

Let’s start with an example of how to use SQL to sum numeric values. The SUM function in SQL will group all of your numbers by type and then count them, giving you one total for each type.

To do it right, you’ll need to put your SUMs in parentheses () when you’re grouping the rows of data.

The syntax is:

Sum([some columns])

It takes all data inside those brackets and counts it up into one number. That’s what we call summing them together into one number.

For example, you want to calculate the total number of people who work at your company. That will be as follows:

SELECT COUNT(*) FROM employees; 

The function has three arguments:

• the expression
• the column name
• the list of constants or variables separated by commas.

If we wanted to find the sum of the numbers 2 and 3 using SQL, we would type the query:

SELECT SUM(2+3) AS 'Sum' FROM Numbers

What’s the Difference Between SQL SUM Function and SQL AVG?

The difference between these two SQL aggregate functions is that the SQL AVG function only calculates an average of all values in a column. The SUM function returns the sum of all values in a column.

How About an Example of SUM in SQL?

Here’s a visual:

Oracle’s SUM OVER – a Specific Use Case

You can find modifications of SUM in languages and instances like this one below. Oracle’s SUM OVER function calculates the sum for each value in a group of values. It does this by taking an expression, evaluating it on all the members of the group, and then returning the result as if they were summed together.

The keyword OVER specifies which rows you need to include in calculations. It is also possible to specify multiple conditions with different levels of nesting using parenthesis.

Is There Anything More to SUM in SQL?

SUM is far more flexible than just getting you the total of something. For example, we can combine it with WHERE and calculate the total of salaries of employees with salaries over 100 000:

SELECT SUM(salary) AS "Total Salary"
FROM employees
WHERE salary > 100000;

Or, assume you want to find out how many employees have a given skill. You could use this code:

SELECT COUNT(*) 
FROM EMPLOYEE 
WHERE SKILL = 'SQL';

Another case is using SQL SUM with GROUP BY:

SELECT department, SUM(sales) AS "Total sales"
FROM order_details
GROUP BY department;

Conclusion

The SUM operator in SQL is an aggregate function that performs a mathematical summation of values. It is a nifty little function, especially when used with other operators, and it’s supported by plenty of popular systems.

The scope of this operator is huge. You’d use it whenever you need the definite sum of certain values. For instance, when you want to find out how many students in one given university score more than 90% in their exams to rank universities, you can use the SUM function.