SUM Function in SQL – Syntax and Cases With Examples

In this tutorial, we will understand in detail the SUM function in SQL. Let us now try to learn more about the Sum function with examples.

What are the Aggregate Functions in SQL?

Aggregate functions are the built-in methods that help us to convert multiple values available in the column to a single entity.

These are required to combine and draw insights from the data spread in columns of the database. Aggregate functions can also be applied to the expressions.

Some of the most popular SQL aggregate functions are as follows:

Where do we use the SUM Function in SQL?

We use the Sum function wherever we need to find the total of an expression or a column.

1. SUM(colName): Returns the sum of all values in the column colName.
Syntax:

SELECT SUM(col1) AS alias,
  Col2 , col3,.....
  FROM tableName;

2. Sum(DISTINCT colName): Returns the sum of distinct non-null values in the column colName.
Syntax:

SELECT SUM(DISTINCT col1) AS alias,
  Col2 , col3,.....
  FROM tableName;

Demo DataBase

Let us now view our demo database and understand it’s contents.
Query: 

SELECT * FROM DataFlair;

Output:

In our DataFlair database, we have five columns indicating the details of all employees at DataFlair. The columns available are as follows:

• Name
• Age
• Employee_id
• Location
• Experience

Let us now work on examples using the DataFlair database and try to understand the Sum() function in SQL.

Example 1: Let us find the total of age of all employees.
Query: 

SELECT SUM(age) as totalAge FROM dataflair;

Output:

In the result, we can see the sum of age of all the employees is visible.

Example 2: Let us now find the sum of distinct age of employees from our DataFlair database.
Query:

SELECT SUM(DISTINCT age) as totalAge FROM dataflair;

Output:

As we have already seen we don’t have duplicate age, thus the total remains the same.

Example 3: Let us now find the sum of age of employees location wise.
Query:

SELECT location as Current_Location ,
SUM(DISTINCT age) as LocationWiseAge FROM dataflair
group by location;

Output:

In the result, we can see the total age of employees at each office location of DataFlair.

Example 4: Let us now find the sum of employees at Pune and Indore office respectively.
Query:

SELECT location as Current_Location ,
SUM(DISTINCT age) as LocationWiseAge FROM dataflair
Where location='Pune' or location ='Indore'
group by location;

Output:

In this result, we can see the sum of age of employees working at Pune and Indore office respectively.

Summary

In this tutorial, we have understood how and when to use the SQL sum function. We have understood all the use cases of the sum function.

Also, we have discussed various examples based on various use cases. Sum function ignores the null values present in our database.

When we use the Sum function we need to be careful as sum function can only be used on numeric data types. If we apply a sum function on non-numeric data type columns we will run into an error and our query will not run.