Calculate the total sum. This function returns the sum of all values in a numeric expression, ignoring NULL values.
Returns the sum of expr. If the return set has no rows, SUM() returnsNULL. The DISTINCT keyword can be used to sum only the distinct values of expr.
SUM() can be used as a , although not with the DISTINCT specifier.
Commonly, SUM is used with a clause:
The clause is required when using an aggregate function along with regular column data, otherwise the result will be a mismatch, as in the following common type of mistake:
As a :
(average)
(maximum)
(minimum)
This page is licensed: GPLv2, originally from
SUM([DISTINCT] expr)CREATE TABLE sales (sales_value INT);
INSERT INTO sales VALUES(10),(20),(20),(40);
SELECT SUM(sales_value) FROM sales;
+------------------+
| SUM(sales_value) |
+------------------+
| 90 |
+------------------+
SELECT SUM(DISTINCT(sales_value)) FROM sales;
+----------------------------+
| SUM(DISTINCT(sales_value)) |
+----------------------------+
| 70 |
+----------------------------+CREATE TABLE sales (name CHAR(10), month CHAR(10), units INT);
INSERT INTO sales VALUES
('Chun', 'Jan', 75), ('Chun', 'Feb', 73),
('Esben', 'Jan', 43), ('Esben', 'Feb', 31),
('Kaolin', 'Jan', 56), ('Kaolin', 'Feb', 88),
('Tatiana', 'Jan', 87), ('Tatiana', 'Feb', 83);
SELECT name, SUM(units) FROM sales GROUP BY name;
+---------+------------+
| name | SUM(units) |
+---------+------------+
| Chun | 148 |
| Esben | 74 |
| Kaolin | 144 |
| Tatiana | 170 |
+---------+------------+SELECT name,SUM(units) FROM sales
;+------+------------+
| name | SUM(units) |
+------+------------+
| Chun | 536 |
+------+------------+CREATE OR REPLACE TABLE student_test (name CHAR(10), test CHAR(10), score TINYINT);
INSERT INTO student_test VALUES
('Chun', 'SQL', 75), ('Chun', 'Tuning', 73),
('Esben', 'SQL', 43), ('Esben', 'Tuning', 31),
('Kaolin', 'SQL', 56), ('Kaolin', 'Tuning', 88),
('Tatiana', 'SQL', 87);
SELECT name, test, score, SUM(score) OVER (PARTITION BY name) AS total_score FROM student_test;
+---------+--------+-------+-------------+
| name | test | score | total_score |
+---------+--------+-------+-------------+
| Chun | SQL | 75 | 148 |
| Chun | Tuning | 73 | 148 |
| Esben | SQL | 43 | 74 |
| Esben | Tuning | 31 | 74 |
| Kaolin | SQL | 56 | 144 |
| Kaolin | Tuning | 88 | 144 |
| Tatiana | SQL | 87 | 87 |
+---------+--------+-------+-------------+