Basic about SUM Function – using and example

This SQL tutorial explains how to use the SQL SUM function with syntax and examples.

Description

The SQL SUM feature is used to return the sum of an expression in a SELECT statement.

Syntax

The syntax for the SUM function in SQL is:

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

OR the syntax for the SUM feature when grouping the results by way of one or greater columns is:

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

Parameters or Arguments

expression1, expression2, … expression_n Expressions that are not encapsulated inside the SUM function and must be included in the GROUP BY clause at the end of the SQL statement. aggregate_expression This is the column or expression that will be summed. tables The tables that you wish to retrieve information from. There should be at least one desk listed in the FROM clause. WHERE conditions Optional. These are stipulations that must be met for the archives to be selected.

Example – With Single Expression

For example, you may want to recognize how the mixed total salary of all personnel whose income is above $25,000 / year.

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

In this SQL SUM Function example, we have aliased the SUM(salary) expression as “Total Salary”. As a result, “Total Salary” will display as the field name when the result set is returned.

Example – Using SQL DISTINCT

You can use the SQL DISTINCT clause within the SQL SUM function. For example, the SQL SELECT assertion under returns the blended total salary of unique earnings values the place the income is above $25,000 / year.

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

If there had been two salaries of $30,000/year, only one of these values would be used in the SQL SUM function.

Example – Using Formula

The expression contained within the SQL SUM function does no longer need to be a single field. You could also use a formula. For example, you may desire the net profits for a business. Net Income is calculated as whole earnings much less whole expenses.

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

You would possibly additionally prefer to perform a mathematical operation within the SQL SUM function. For example, you would possibly decide whole fee as 10% of total sales.

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

Example – Using SQL GROUP BY

In some cases, you will be required to use the SQL GROUP BY clause with the SQL SUM function.

For example, you should additionally use the SQL SUM feature to return the name of the branch and the complete sales (in the associated department).

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

Because you have listed one column in your SQL SELECT declaration that is not encapsulated in the SQL SUM function, you have to use the SQL GROUP BY clause. The department subject must, therefore, be listed in the SQL GROUP BY section.