SUM

Syntax

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

Description

Returns the total of expr. If no rows are returned in the result set, SUM() returns NULL. You can use the DISTINCT keyword to return the total of unique values of expr.

If no rows match the condition, SUM() returns NULL.

Examples

CREATE TABLE EXPLOYEES(LAST_NAME CHAR(10), SALARY DECIMAL, JOB_ID CHAR(32));
Query OK, 0 rows affected (0.056 sec)

INSERT INTO EXPLOYEES VALUES('JIM', 2000, 'CLEANER');
Query OK, 1 row affected (0.001 sec)

INSERT INTO EXPLOYEES VALUES('MIKE', 12000, 'ENGINEERING');
Query OK, 1 row affected (0.001 sec)

INSERT INTO EXPLOYEES VALUES('LILY', 13000, 'ENGINEERING');
Query OK, 1 row affected (0.001 sec)

INSERT INTO EXPLOYEES VALUES('TOM', 11000, 'ENGINEERING');
Query OK, 1 row affected (0.001 sec)

SELECT LAST_NAME, SUM(SALARY) OVER (PARTITION BY JOB_ID) TOTAL_S FROM EXPLOYEES;
+-----------+---------+
| LAST_NAME | TOTAL_S |
+-----------+---------+
| JIM       |    2000 |
| MIKE      |   36000 |
| LILY      |   36000 |
| TOM       |   36000 |
+-----------+---------+
4 rows in set (0.003 sec)