SUM Function in SQL – Syntax and Cases With Examples

by DataFlair Team · November 19, 2018

1. Objective – SQL SUM Function

Today, in this SQL tutorial, we are going to study the SUM function in SQL. Moreover, we will look at the syntax and examples of the SQL SUM Function. Also, we will see the two cases in SUM Function SQL that are using single expression and SQL Distinct.

So, let us start the SUM Function in SQL Tutorial.

SUM Function in SQL

Have a look at SQL Data Types

2. Introduction to SUM Function in SQL

We use the SQL SUM work to restore the sum of a statement in a SELECT statement.

The syntax of SUM Function in SQL –

The syntax structure for the SUM work in SQL is:

SELECT SUM(SUM_expression)
FROM tables
[WHERE conditions];

Or then again the syntax structure for the SUM function when gathering the outcomes by at least one statement is:

SELECT expression1, expression2, ... expression_n,
SUM(SUM_expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, ... expression_n;

3. Parameters or Arguments

expression1, expression2, … expression_n

Statement that is not epitomized inside the SUM work and should be incorporated into the GROUP BY proviso toward the finish of the SQL explanation.

Learn more about SQL Alter Command

SUM_expression

This is the segment or statement that will sum.

tables

The tables that you wish to recover records from. There must be somewhere around one table recorded in the FROM statement.

WHERE conditions

Discretionary. These are conditions that must be met for the records to be chosen.

i. Case – With Single Expression

For instance, you may wish to know how the consolidated SUM pay of all workers whose compensation is above $25,000/year.

You must visit the latest SQL interview questions

SELECT SUM(salary) AS "Add up to Salary"
FROM workers
WHERE pay > 25000;

In this SQL SUM Function case, we’ve associated the SUM(salary) statement as “Add up to Salary”. Therefore, “Add up to Salary” will show as the field name when the outcome set is returned.

ii. Case – Using SQL DISTINCT

You can use the SQL DISTINCT statement inside the SQL SUM work. For instance, the SQL SELECT explanation beneath restores the joined SUM compensation of interesting pay esteems where the pay is above $25,000/year.

Do you know about SQL injection

SELECT SUM(DISTINCT compensation) AS "Add up to Salary"
FROM representatives
WHERE compensation > 25000;

On the off chance that there were two pay rates of $30,000/year, just a single of these qualities would be used in the SQL SUM work.

iii. Example 1- Using Formula

The statement contained inside the SQL SUM work should not be a single field. You could likewise use an equation. For instance, you may need the net salary for a business. Net Income is figured as SUM pay fewer SUM costs.

SELECT SUM(income - costs) AS "Net Income"
FROM gl_transactions;

You may likewise need to play out a scientific activity inside the SQL SUM work. For instance, you may decide add up to the commission as 10% of SUM deals.

SELECT SUM(sales * 0.10) AS "Commission"
FROM order_details;

iv. Example 2- Using SQL GROUP BY

At times, you will require to use the SQL GROUP BY condition with the SQL SUM work.

For instance, you could likewise use the SQL SUM capacity to restore the name of the division and the SUM deals (in the related office).

Let’s revise SQL Clause

SELECT division, SUM(sales) AS "Add up to deals"
FROM order_details
SUM BY division;

Since you have recorded one segment in your SQL SELECT segment that isn’t typified in the SQL SUM work, you should use the SQL GROUP BY statement. The division field must, in this manner, will record in the SQL GROUP BY area.

So, this was all about SUM Function in SQL. Hope you like our explanation

4. Conclusion – SQL SUM Function

Hence, in this tutorial, we studied the SUM function in SQL. Moreover, we looked at the syntax for SQL SUM Function. Also, we discussed the parameters or arguments of SUM Function in SQL. Along with this, we looked at 2 different cases that are SQL with a single expression and using SQL Distinct. At last, we explained the SUM Function in SQL with the help of examples.

Still, if you have any query regarding SUM Function in SQL, ask in the comment tab.

See also –

COUNT Function in SQL

Reference for SQL