MariaDB SUM() Function

In MariaDB, SUM() is a built-in function that returns the sum of all specified non-NULL values.

MariaDB `SUM()` Syntax

Here is the syntax of the MariaDB SUM() function:

SUM(expr)

We typically use the SUM() function like this:

SELECT SUM(expr), ...
FROM table_name
[WHERE ...];

Or use the SUM() function with the GROUP BY clause:

SELECT SUM(expr), group_expr1, group_expr2, ...
FROM table_name
[WHERE ...]
GROUP BY group_expr1, group_expr2, ...;

Parameters

expr: The expr expression used for aggregation operations. It can be a column name or an expression.
group_expr1, group_expr2, ...: The expression or column name used for grouping.

Return value

The MariaDB SUM(expr) function returns the sum of all specified non-NULL values.

If there is no matching row, the SUM() function returns NULL.

MariaDB `SUM()` Examples

We’ll demonstrate with the student_score table. Let’s first create a demo table and insert test rows.

DROP TABLE IF EXISTS `student_score`;
CREATE TABLE `student_score` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `name` VARCHAR(255) NOT NULL,
    `subject` VARCHAR(255) NOT NULL,
    `score` INT
);
INSERT INTO `student_score` (`name`, `subject`, `score`)
VALUES ('Tom', 'Math', 80),
    ('Tom', 'English', 90),
    ('Jim', 'Math', 84),
    ('Jim', 'English', 96),
    ('Tim', 'Math', NULL),
    ('Tim', 'English', 98);

Here is all the rows in the table:

+----+------+---------+-------+
| id | name | subject | score |
+----+------+---------+-------+
|  1 | Tom  | Math    |    80 |
|  2 | Tom  | English |    90 |
|  3 | Jim  | Math    |    84 |
|  4 | Jim  | English |    96 |
|  5 | Tim  | Math    |  NULL |
|  6 | Tim  | English |    98 |
+----+------+---------+-------+

Basic example

The following SQL statement returns the sum of all scores in the student_score table.

SELECT SUM(score) from student_score;

+------------+
| SUM(score) |
+------------+
|        448 |
+------------+

`GROUP BY`

Let’s use SUM() and GROUP BY to get the total score for more dimensions.

Total score for each subject

SELECT subject, SUM(score)
FROM student_score
GROUP BY subject;

+---------+------------+
| subject | SUM(score) |
+---------+------------+
| Math    |        164 |
| English |        284 |
+---------+------------+

Here, MySQL will first group the result set by subject according to GROUP BY subject, and then execute SUM(score) within each group.

Total score for each student

SELECT name, SUM(score)
FROM student_score
GROUP BY name;

+------+------------+
| name | SUM(score) |
+------+------------+
| Tom  |        170 |
| Jim  |        180 |
| Tim  |         98 |
+------+------------+

Here, MySQL will first group the result set by name according to GROUP BY name, and then execute SUM(score) within each group.

Conclusion

In MariaDB, SUM() is a built-in function that returns the sum of all specified non-NULL values.

MariaDB SUM() Syntax