SUM()
This page is part of MariaDB's Documentation.
The parent of this page is: Functions for MariaDB Xpand
Topics on this page:
Overview
Returns the sum of the aggregated values.
USAGE
SUM([ALL | DISTINCT] number)
Argument Name | Description |
|---|---|
| Optional. Controls if duplicates are ignored. Default: ALL |
| The number that is to be summed |
DETAILS
SUM() is an aggregate function that returns the sum of a set of numeric values.
Duplicate values in the aggregated data can be dropped from the result by specifying the DISTINCT clause at the start of the arguments. The default is to add all values together to get the sum, which can be explicitly requested with the ALL clause.
Any NULL values are completely ignored.
A NULL is returned if no numeric values are aggregated.
EXAMPLES
Example Schema and Data
All of the examples are based on the inventory table:
CREATE TABLE inventory (
item VARCHAR(20),
category VARCHAR(20),
count INT
);
INSERT INTO inventory VALUES
('Foo','in stock', 42), ('Foo','back order', 50),
('Bar','in stock', 10), ('Bar','back order', 60),
('Baz','in stock', 99), ('Baz','back order', 10),
('FooBar','in stock', 10), ('FooBar','back order', NULL),
('BarBaz','in stock', NULL), ('BarBaz','back order', NULL);
The values to be summed:
SELECT item, count
FROM inventory;
+--------+-------+
| item | count |
+--------+-------+
| Foo | 42 |
| Foo | 50 |
| Bar | 10 |
| Bar | 60 |
| Baz | 99 |
| Baz | 10 |
| FooBar | 10 |
| FooBar | NULL |
| BarBaz | NULL |
| BarBaz | NULL |
+--------+-------+
ALL Clause (default behavior)
By default all values are added together into the sum, which can also be explicitly requested with the ALL clause:
SELECT SUM(ALL count), SUM(count)
FROM inventory;
+----------------+------------+
| SUM(ALL count) | SUM(count) |
+----------------+------------+
| 281 | 281 |
+----------------+------------+
DISTINCT Clause
Using the DISTINCT clause drops duplicate input values from the summation:
SELECT SUM(DISTINCT count)
FROM inventory;
+---------------------+
| SUM(DISTINCT count) |
+---------------------+
| 261 |
+---------------------+
With GROUP BY
Using SUM() with GROUP BY creates a separate sum for each group. In this example, GROUP BY category creates a separate sum for each unique category:
SELECT category, SUM(count)
FROM inventory
GROUP BY category
ORDER BY category;
+------------+------------+
| category | SUM(count) |
+------------+------------+
| back order | 120 |
| in stock | 161 |
+------------+------------+
With Only NULL Values
A NULL is returned if no numeric values are aggregated:
SELECT SUM(count)
FROM inventory
WHERE item = 'BarBaz';
+------------+
| SUM(count) |
+------------+
| NULL |
+------------+