SUM

MySQL

SUM(expression)

• expression: The column or set of values to be added together. The `expression` is evaluated for each row in the table and the result is summed. If the `expression` results in `NULL`, it is treated as zero. The `expression` must be of type `INT`, `FLOAT`, `DECIMAL`, or any other numeric type.

Example

SELECT SUM(salary) AS TotalSalary
FROM employees;

Output

+--------------+
| TotalSalary  |
+--------------+
|   10000000   |
+--------------+

Explanation

The aforementioned SQL statement calculates the sum of all values in the ‘salary’ column from the ‘employees’ table. The result is then labelled as ‘TotalSalary’.

PostgreSQL

SUM(expression) OVER ([partition_by_clause] [order_by_clause] [frame_clause])

• expression: The column or expression over which the SUM function is to be applied. This is usually a column of numerical data type.
• partition_by_clause: This is an optional component that divides the result set produced by FROM clause into partitions to which the function is applied, essentially functioning in a similar way as GROUP BY clause in SQL.
• order_by_clause: Another optional component, which sorts the data in each partition. It defines the logical order, within a partition, in which the rows will be processed.
• frame_clause: The last optional component, it further refines the result set for each row to a subset of each partition. The rows selected by the frame_clause are referred to as a window, defined by a range between two boundary points.

Example

SELECT SUM(salary) FROM employees;

Output

 sum
-------
 75000

Explanation

The example gives the total sum of all salaries from the employees table.

SQL Server

SUM( [ ALL ] expression ) OVER ( [ partition_by_clause ] order_by_clause rows_range_clause )

• all: This optional parameter allows the function to include duplicates in the calculation.
• expression: Refers to the specific column or set of columns that will be summed up in the query.
• over: This part of the command defines the window or set of rows where the SUM function is applied.
• partition_by_clause: This parameter separates the data into different partitions (groups), which can be individually operated on, allowing calculations on a more granular level.
• order_by_clause: Specifies the order in which rows in a partition should be sorted before the SUM function is applied.
• rows_range_clause: Further limits the rows within the window (specified by OVER clause) over which the SUM function is calculated, based on a certain range or set of rows.

Example

SELECT SUM(OrderAmount) as TotalOrderAmount
FROM Orders;

Output

TotalOrderAmount
----------------
25000

Explanation

The SQL statement calculates the cumulative sum of the amount for all the orders in the ‘Orders’ table, under the column ‘OrderAmount’. The result is labeled ‘TotalOrderAmount’.

Oracle

SUM([ALL | DISTINCT] expression) OVER ([query_partition_clause] [order_by_clause [windowing_clause]])

• all: Used with SUM function to return the sum of all values, including duplicates, of the specified expression.
• distinct: Combined with SUM to provide the total of unique values in the expression.
• expression: The column or formula from which the sum will be calculated.
• over: Specifies that the SUM function is to be applied over a certain range of rows defined by some specified criteria.
• query_partition_clause: Divides the result set produced by the FROM clause into partitions to which the function SUM is applied.
• order_by_clause: Based on the ordering specified by this clause, the SUM function is computed cumulatively for each row in ascending order.
• windowing_clause: This clause further defines or restricts the set of rows (window) over which the SUM function is working on.

Example

SELECT SUM(salary)
FROM employees;

Output

SUM(SALARY)
-----------
50000

Explanation

In the given example, the SUM function is used to add up all the values in the ‘salary’ column of the ‘employees’ table. The result, 50000, is the total sum of all salaries in the ‘employees’ table.

SQLite

SUM(expression)

• expression: This represents the column or set of values that the SUM function is to be applied to. The Expression must be made up of values of the numeric data type such as integer, decimal, and float.

Example

SELECT SUM(Salary)
FROM Employees;

Output

Total_Salary
---------------
72000

Explanation

In this example, the SUM() function is used to calculate the total sum of the ‘Salary’ column from the ‘Employees’ table. The result, 72000, represents the total sum of all salaries.