SQL SUM function

SQL SUM function is used to return the sum of an expression in the SELECT operator.

Syntax for SUM function in SQL

SELECT SUM(aggregate_expression_id)
FROM tabs
[WHERE conds]

Or syntax for SUM function when grouping results by one or more columns.

SELECT expression1_id, expression2_id, ...expression_n_id,
SUM(aggregate_expression_id)
FROM tabs
[WHERE conds]
GROUP BY expression1_id, expression2_id, ...expression_n_id;

where:

• expression1_id, expression2_id, …_n_id – Expressions that are not encapsulated in the SUM function and must be included in the GROUP BY offer at the end of the SQL query
• aggregate_expression_id – It’s a column or an expression to be summed up
• tabs – The tables from which you want the records. The FROM sentence must contain at least one table
• WHERE conds – It’s optional. These are the conditions that must be met to select records

Single Expression Example

For example, you may want to know how the total gross salary of all employees with salaries in excess of $25,000 per year is.

SELECT SUM(salary_id) AS "Total Salary_id"
FROM empls
WHERE salary_id > 25,000;

In this example of the SQL function SUM we have assigned the alias expression SUM(salary_id) as “Total Salary_id”. As a result “Total Salary_id” will be displayed as a field name when returning the result set.

Using SQL DISTINC

You can use DISTINCT SQL sentence in SUM function. For example, the following SQL statement SELECT returns the total “total salary_id” with unique values where the salary_id exceeds $25000 per year.

SELECT SUM(DISTINCT salary_id) AS "Total Salary_id"
FROM empls
WHERE salary_id > 25,000;

If two salaries were $30,000 per year, the SUM SQL function would use only one of these values.

Using formulas

The expressions contained in the SUM SQL function do not necessarily have to be a single field. You can also use a formula. For example, you may need a net income for your business. Net income_id is calculated as total income_id minus total expenses_id.

SELECT SUM(income_id - expenses_id) AS "Net Income_id"
FROM gl_transactions_id;

You can also execute a mathematical operation in SUM SQL function. For example, you can define total commission as 10% of total sales.

SELECT SUM(sales_id * 0.10) AS "Commission_id"
FROM ord_details;

Using SQL GROUP BY

In some cases you will need to use GROUP BY SQL statement with SUM function.

For example, you can also use SUM SQL function to return the department name and general sales (in a related department_id).

SELECT department_id,
SUM(sales) AS "Total sales_id"
FROM ord_details
GROUP BY department_id;

Therefore the department field must be specified in the GROUP BY SQL statement.

Fundamentals of SQL Using Sum Function