All Products
Search
Document Center

SUM

Last Updated: Jun 18, 2021

The SUM function returns the sum of values in a column that is specified by a parameter. This function uses the numeric or non-numeric data types that can be implicitly converted to numeric data types as parameters. The function returns the data type that is the same as the numeric data type of the parameter.

Syntax

SUM([ DISTINCT | UNQIUE | ALL ] expr) [ OVER (analytic_clause) ]

If the function is used as an analytic function, you must use the full syntax of a window function. The function calculates a set of rows and returns multiple values. If the function is used as an aggregate function, the function aggregates a set of rows and returns only one value. In this case, you do not need to add the OVER keyword.

Parameters

Parameter

Description

DISTINCT

Removes duplicate rows and ignores the rows whose values are NULL.

UNQIUE

Removes duplicate rows and ignores the rows whose values are NULL.

ALL

Returns all the values, including duplicate rows, and ignores the rows whose values are NULL.

expr

The data column or expression of the numeric type, character type, date type, or other types.

OVER

Uses the OVER clause to define a window for calculation.

Notice

:

If you specify the DISTINCT or UNIQUE keyword, order_by_clause and windowing_clause cannot appear in analytic_clause.

Return type

The value of the data type same as the data type of expr is returned.

Examples

Examples of the analytic function

To create the employees table and insert data into the table, execute the following statements:

CREATE TABLE employees(manager_id INT,last_name varchar(50),hiredate varchar(50),SALARY INT);
INSERT INTO employees VALUES(300, 'Wei',  '2019-09-11',23600);     
INSERT INTO employees VALUES(200, 'Red', '2019-11-05', 23800);
INSERT INTO employees VALUES(100, 'Part',  '2018-10-01',24000);     
INSERT INTO employees VALUES(200, 'Ross',  '2019-06-11',23500);     
INSERT INTO employees VALUES(200, 'Bell', '2019-05-25', 23000);
INSERT INTO employees VALUES(200, 'Part',  '2018-06-11',24500);    
INSERT INTO employees VALUES(100, 'De Haan', '2018-05-01',11000);      
INSERT INTO employees VALUES(100, 'Errazuriz', '2017-07-21', 1400);
INSERT INTO employees VALUES(100, 'Hartstein', '2019-05-01',14000);     
COMMIT;

To calculate the sum of salaries, execute the following statement:

SELECT manager_id, last_name, salary, SUM(salary) OVER (PARTITION BY manager_id 
ORDER BY salary RANGE UNBOUNDED PRECEDING) l_csum
FROM employees ORDER BY manager_id, last_name, salary, l_csum;

The following query result is returned:

+------------+-----------+--------+--------+
| MANAGER_ID | LAST_NAME | SALARY | L_CSUM |
+------------+-----------+--------+--------+
|        100 | De Haan   |  11000 |  12400 |
|        100 | Errazuriz |   1400 |   1400 |
|        100 | Hartstein |  14000 |  26400 |
|        100 | Part      |  24000 |  50400 |
|        200 | Bell      |  23000 |  23000 |
|        200 | Part      |  24500 |  94800 |
|        200 | Red       |  23800 |  70300 |
|        200 | Ross      |  23500 |  46500 |
|        300 | Wei       |  23600 |  23600 |
+------------+-----------+--------+--------+

Examples of the aggregate function

To calculate the sum of salaries, execute the following statement:

SELECT SUM(salary) FROM employees;

The following query result is returned:

+-------------+
| SUM(SALARY) |
+-------------+
|      168800 |
+-------------+