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:

Sr.No Function Description
1 AVG() Returns the average value.
2 COUNT() Returns the count.
3 MAX() Returns the max out of given values.
4 MIN() Returns minimum out of the given values.
5 SUM() Returns the sum of the given values.
6 VAR() Returns variance of the given values.

Stay updated with latest technology trends
Join DataFlair on Telegram!!

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:

SQL Demo Database

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:

SQL Sum() Example

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:

SQL Sum Function

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:

Sum() in SQL

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:

SQL Sum Function Example

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.

Did you know we work 24x7 to provide you best tutorials
Please encourage us - write a review on Google | Facebook

Leave a Reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.