SUM([DISTINCT] expr)
Returns the sum of expr
. If the return set has no rows, SUM()
returns NULL
. The DISTINCT
keyword can be used to sum only the distinct values of expr
.
From MariaDB 10.2.0, SUM() can be used as a window function, although not with the DISTINCT specifier.
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 | +----------------------------+
Commonly, SUM is used with a GROUP BY clause:
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 | +---------+------------+
The GROUP BY 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:
SELECT name,SUM(units) FROM sales ;+------+------------+ | name | SUM(units) | +------+------------+ | Chun | 536 | +------+------------+
As a window function:
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 | +---------+--------+-------+-------------+
© 2019 MariaDB
Licensed under the Creative Commons Attribution 3.0 Unported License and the GNU Free Documentation License.
https://mariadb.com/kb/en/sum/