​
Overview

SUM() calculates the sum of values from stored records. SUM() doesn’t consider NULL in the calculation, and it returns NULL instead of zero if the executed statement returns no rows.

The input and return types we support can be seen in the table below.

Input typeReturn type
INTLONG
LONGLONG
FLOATDOUBLE
DOUBLEDOUBLE
INTERVALINTERVAL
If the input type is 32-bit, then the result will be 64-bit.

​
Examples

We have two sample tables here:

customer table

CREATE TABLE customer (
  customer_id int,
  cust_name string
);
INSERT INTO customer
    (customer_id, cust_name)
VALUES
    (11112, 'Alex'),
    (11113, 'Aaron'),
    (11114, 'Alice'),
    (11115, 'Nina'),
    (11116, 'Rosy'),
    (11117, 'Martha'),
    (11118, 'John');

SELECT * FROM customer;

It will create a table as shown below:

+-------------+-----------+
| customer_id | cust_name |
+-------------+-----------+
| 11112       | Alex      |
| 11113       | Aaron     |
| 11114       | Alice     |
| 11115       | Nina      |
| 11116       | Rosy      |
| 11117       | Martha    |
| 11118       | John      |
+-------------+-----------+

rental table

CREATE TABLE rental (
    rental_id int,
    rental_date timestamp,
    return_date timestamp,
    car string,
    customer_id int,
    total_price int
);
INSERT INTO rental (rental_id, rental_date, return_date, car, customer_id, total_price)
VALUES
(8557411, '2022-04-02 09:10:19', '2022-04-10 10:15:05', 'Audi', 11112, 1400),
(8557421, '2022-04-06 07:00:30', '2022-04-19 07:10:19', 'BMW', 11115, 2000),
(8557451, '2022-04-19 08:00:20', '2022-04-24 08:05:00', 'Cadillac', 11112, 1000),
(8557311, '2022-05-11 09:15:28', '2022-05-18 09:00:18', 'Audi', 11115, 1500),
(8557321, '2022-05-20 10:12:22', '2022-05-28 10:08:48', 'Audi', 11113, 1500),
(8557351, '2022-06-10 12:18:09', '2022-06-20 18:12:23', 'Cadillac', 11114, 1200),
(8557221, '2022-06-17 14:02:02', '2022-06-20 14:17:02', 'Chevrolet', 11112, 1300),
(8557251, '2022-07-12 05:19:49', '2022-07-19 07:15:28', 'Chevrolet', 11116, 1400),
(8557231, '2022-08-09 09:29:08', '2022-08-24 09:30:58', 'Cadillac', 11114, 2000),
(8557291, '2022-08-18 15:15:20', '2022-09-01 15:30:19', 'BMW', 11117, 3000);

SELECT * FROM rental;

Here, we have a rental table which stores the details for car rental:

+------------+---------------------+---------------------+-----------+---------------+-------------+
| rental_id  | rental_date         | return_date         | car       | customer_id   | total_price |
+------------+---------------------+---------------------+-----------+---------------+-------------+
| 8557411    | 2022-04-02 09:10:19 | 2022-04-10 10:15:05 | Audi      | 11112         | 1400        |
| 8557421    | 2022-04-06 07:00:30 | 2022-04-19 07:10:19 | BMW       | 11115         | 2000        |
| 8557451    | 2022-04-19 08:00:20 | 2022-04-24 08:05:00 | Cadillac  | 11112         | 1000        |
| 8557311    | 2022-05-11 09:15:28 | 2022-05-18 09:00:18 | Audi      | 11115         | 1500        |
| 8557321    | 2022-05-20 10:12:22 | 2022-05-28 10:08:48 | Audi      | 11113         | 1500        |
| 8557351    | 2022-06-10 12:18:09 | 2022-06-20 18:12:23 | Cadillac  | 11114         | 1200        |
| 8557221    | 2022-06-17 14:02:02 | 2022-06-20 14:17:02 | Chevrolet | 11112         | 1300        |
| 8557251    | 2022-07-12 05:19:49 | 2022-07-19 07:15:28 | Chevrolet | 11116         | 1400        |
| 8557231    | 2022-08-09 09:29:08 | 2022-08-24 09:30:58 | Cadillac  | 11114         | 2000        |
| 8557291    | 2022-08-18 15:15:20 | 2022-09-01 15:30:19 | BMW       | 11117         | 3000        |
+------------+---------------------+---------------------+-----------+---------------+-------------+

​
#Case 1: SUM() in SELECT statement

The following example uses the SUM() function to calculate the total rent price of all rental_id:

SELECT SUM (total_price) AS total
FROM rental

It will return a sum value of the total_price:

+--------+
| total  |
+--------+
| 16300  |
+--------+

​
#Case 2: SUM() with a NULL result

The following example uses the SUM() function to calculate the total rent price of the customer_id = 11118.

SELECT SUM (total_price) AS total
FROM rental
WHERE customer_id = 11118;

Since no records in the rental table have the customer_id = 11118, the SUM() function returns a NULL.

+--------+
| total  |
+--------+
| null   |
+--------+

​
#Case 3: SUM() with GROUP BY clause

You can use the GROUP BY clause to group the records in the table and apply the SUM() function to each group afterward.

The following example uses the SUM() function and the GROUP BY clause to calculate the total price paid by each customer:

SELECT customer_id,
SUM (total_price) AS total_spend
FROM rental
GROUP BY customer_id;

It will calculate the total_price from a group of customer_id as shown below:

+--------------+--------------+
| customer_id  | total_spend  |
+--------------+--------------+
| 11115        | 3500         |
| 11117        | 3000         |
| 11116        | 1400         |
| 11113        | 1500         |
| 11112        | 3700         |
| 11114        | 3200         |
+--------------+--------------+

​
#Case 4: SUM() with HAVING clause

You can use the SUM() function with the HAVING clause to filter out the sum of groups based on a specific condition:

SELECT
    customer_id,
    SUM (total_price) AS total_spend
FROM rental
GROUP BY customer_id
HAVING SUM(total_price) >= 3000;

It will return the customers who spent greater than or equal to 3000:

+--------------+--------------+
| customer_id  | total_spend  |
+--------------+--------------+
| 11115        | 3500         |
| 11117        | 3000         |
| 11112        | 3700         |
| 11114        | 3200         |
+--------------+--------------+

​
#Case 5: SUM() with multiple expression

The example uses the following:

  • SUM() function to calculate total rental days.

  • JOIN clause to combine the rental table with the customer table.

  • GROUP BY group a result-set based on the customers’ names.

SELECT s.cust_name, SUM(return_date - rental_date ) AS rental_period
FROM rental AS r
JOIN customer AS s
ON r.customer_id = s.customer_id
GROUP BY cust_name;

The final result will display the customers’ names with their total rental period.

+------------+-------------------+
| cust_name  | rental_period     |
+------------+-------------------+
| Aaron      | 7 days 23:56:26   |
| Martha     | 14 days 00:14:59  |
| Rosy       | 7 days 01:55:39   |
| Nina       | 19 days 23:54:39  |
| Alex       | 16 days 01:24:26  |
| Alice      | 25 days 05:56:04  |
+------------+-------------------+
MINDISTINCT qualifier