SUM

19min
You’ll learn how to use the SUM() function to calculate the sum of a set of values.
OverviewSUM() 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.
﻿
👨🏻‍💻 If the input type is 32-bit, then the result will be 64-bit.
ExamplesWe have two sample tables here:
                                                                                  customer table﻿
Create a 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');
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');
﻿
Display the Table
|
SELECT * FROM customer;
SELECT * FROM customer;
﻿
It will create a table as shown below:
The customer Table
|
+-------------+-----------+
| customer_id | cust_name |
+-------------+-----------+
| 11112       | Alex      |
| 11113       | Aaron     |
| 11114       | Alice     |
| 11115       | Nina      |
| 11116       | Rosy      |
| 11117       | Martha    |
| 11118       | John      |
+-------------+-----------+
+-------------+-----------+
| customer_id | cust_name |
+-------------+-----------+
| 11112       | Alex      |
| 11113       | Aaron     |
| 11114       | Alice     |
| 11115       | Nina      |
| 11116       | Rosy      |
| 11117       | Martha    |
| 11118       | John      |
+-------------+-----------+
﻿
                                                                                  rental table﻿
Create a 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);
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);
﻿
Display the Table
|
SELECT * FROM rental;
SELECT * FROM rental;
﻿
Here, we have a rental table which stores the details for car rental:
The rental Table
|
+------------+---------------------+---------------------+-----------+---------------+-------------+
| 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        |
+------------+---------------------+---------------------+-----------+---------------+-------------+
+------------+---------------------+---------------------+-----------+---------------+-------------+
| 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 statementThe following example uses the SUM() function to calculate the total rent price of all rental_id:
Sum
|
SELECT SUM (total_price) AS total
FROM rental
SELECT SUM (total_price) AS total
FROM rental
﻿
It will return a sum value of the total_price:
Output
|
+--------+
| total  |
+--------+
| 16300  |
+--------+
+--------+
| total  |
+--------+
| 16300  |
+--------+
﻿
#Case 2: SUM() with a NULL resultThe following example uses the SUM() function to calculate the total rent price of the customer_id = 11118.
Sum
|
SELECT SUM (total_price) AS total
FROM rental
WHERE 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.
Output
|
+--------+
| total  |
+--------+
| null   |
+--------+
+--------+
| total  |
+--------+
| null   |
+--------+
﻿
#Case 3: SUM() with GROUP BY clauseYou 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:
Sum
|
SELECT customer_id,
SUM (total_price) AS total_spend
FROM rental
GROUP BY customer_id;
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:
Output
|
+--------------+--------------+
| customer_id  | total_spend  |
+--------------+--------------+
| 11115        | 3500         |
| 11117        | 3000         |
| 11116        | 1400         |
| 11113        | 1500         |
| 11112        | 3700         |
| 11114        | 3200         |
+--------------+--------------+
+--------------+--------------+
| customer_id  | total_spend  |
+--------------+--------------+
| 11115        | 3500         |
| 11117        | 3000         |
| 11116        | 1400         |
| 11113        | 1500         |
| 11112        | 3700         |
| 11114        | 3200         |
+--------------+--------------+
﻿
#Case 4: SUM() with HAVING clauseYou can use the SUM() function with the HAVING clause to filter out the sum of groups based on a specific condition:
Sum
|
SELECT 
    customer_id,
    SUM (total_price) AS total_spend
FROM rental
GROUP BY customer_id
HAVING SUM(total_price) >= 3000;
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:
Output
|
+--------------+--------------+
| customer_id  | total_spend  |
+--------------+--------------+
| 11115        | 3500         |
| 11117        | 3000         |
| 11112        | 3700         |
| 11114        | 3200         |
+--------------+--------------+
+--------------+--------------+
| customer_id  | total_spend  |
+--------------+--------------+
| 11115        | 3500         |
| 11117        | 3000         |
| 11112        | 3700         |
| 11114        | 3200         |
+--------------+--------------+
﻿
#Case 5: SUM() with multiple expressionThe 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.
Sum
|
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;
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.
Output
|
+------------+-------------------+
| 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  |
+------------+-------------------+
+------------+-------------------+
| 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  |
+------------+-------------------+
﻿
﻿