SUM Function in SQL – Syntax and Cases With Examples
Stay updated with the latest technology trends while you're on the move - Join DataFlair's Telegram Channel
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.
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.
This is the segment or statement that will sum.
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.
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.
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).
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 –