MySQL SUM() Function

The MySQL SUM() function computes the sum of all specified values ​​in a group and returns the result.

SUM() Syntax

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

SUM(expr)

We usually 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

An expression used for aggregate operations. It can be a column name or an expression.

group_expr1, group_expr2, ...

An expression or column name to use for grouping.

Return value

The SUM(expr) function returns the sum of all specified values ​​in a group.

If there are no matching rows, the SUM() function returns NULL.

SUM() Examples

We’ll demonstrate this function with a table named student_score. Let’s create the table and insert some rows firstly.

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 the data 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 |
+----+------+---------+-------+

SUM() 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 |
+------------+

SUM() combineGROUP BY

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

1. Query 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 rows by subject according to GROUP BY subject, and then execute SUM(score) within each group.

2. Total score by name

   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 rows by name according to GROUP BY name, and then execute SUM(score) within each group.