SUM
On this page
Calculate the sum of a set of numbers.
NULL values are ignored.
May be used as a window function and with the frame clause.
Syntax
Aggregate Function
SUM ( [DISTINCT] expression )
Window Function & Window Frame Clause
SUM ( [DISTINCT] expr ) OVER ([PARTITION BY expr][ORDER BY expr ] [<window_frame_function>])
Arguments
-
DISTINCT: optional keyword.
If present, will sum the unique values. -
expression: any expression.
This may be a column name, the result of another function, or a math operation.
Return Type
A double if the input type is double, otherwise decimal.
Examples
CREATE TABLE player_scores(player_name VARCHAR(50), player_id VARCHAR(10),1st_qtr_score DECIMAL(5,2), 2nd_qtr_score DECIMAL(5,2),3rd_qtr_score DECIMAL(5,2), 4th_qtr_score DECIMAL(5,2),yearly_total AS 1st_qtr_score + 2nd_qtr_score + 3rd_qtr_score + 4th_qtr_score PERSISTED DECIMAL(5,2));INSERT INTO player_scores VALUES('Steve', '119','22.50','72.00', '63.00', '45.00'),('Jack', '432', '90.10', '78.00','88.20', '92.20'),('Jim', '779','75.00', '68.90','55.70', '72.00'),('Eve', '189','91.50', '88.10', '95.00', '94.50'),('Shelia','338', '75.60', '72.00', '81.10', '78.40'),('June', '521', '81.00', '87.30','76.80','82.20'),('Martin', '674','98.80', '95.10', '88.00', '96.40');
Aggregate Function
SELECT SUM(1st_qtr_score), SUM(2nd_qtr_score) FROM player_scores;
+--------------------+--------------------+
| SUM(1st_qtr_score) | SUM(2nd_qtr_score) |
+--------------------+--------------------+
| 534.50 | 561.40 |
+--------------------+--------------------+Window Function
SELECT player_name, 1st_qtr_score, 2nd_qtr_score,SUM(1st_qtr_score + 2nd_qtr_score) OVER (PARTITION BY 1st_qtr_score ORDER BY 2nd_qtr_score) sum_1st_halfFROM player_scoresORDER BY player_name;
Window Frame Clause
SELECT player_name, 3rd_qtr_score, 4th_qtr_score,SUM(3rd_qtr_score + 4th_qtr_score) OVER (PARTITION BY 3rd_qtr_score order by 4th_qtr_score ROWS between UNBOUNDED PRECEDING and CURRENT ROW) sum_2nd_halfFROM player_scoresORDER BY player_name;
+-------------+---------------+---------------+--------------+
| player_name | 3rd_qtr_score | 4th_qtr_score | sum_2nd_half |
+-------------+---------------+---------------+--------------+
| Eve | 95.00 | 94.50 | 189.50 |
| Jack | 88.20 | 92.20 | 180.40 |
| Jim | 55.70 | 72.00 | 127.70 |
| June | 76.80 | 82.20 | 159.00 |
| Martin | 88.00 | 96.40 | 184.40 |
| Shelia | 81.10 | 78.40 | 159.50 |
| Steve | 63.00 | 45.00 | 108.00 |
+-------------+---------------+---------------+--------------+Last modified: February 23, 2023