SUM

14min
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.
﻿
👨🏻‍💻 If the input type is 32-bit, then the result will be 64-bit.
Examples
We 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 statement
The 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 result
The 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 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:
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 clause
You 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 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.
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  |
+------------+-------------------+
﻿
﻿
Updated 09 Oct 2023
Did this page help you?
Aggregation Functions
MIN
Docs powered by Archbee
TABLE OF CONTENTS
Overview
Examples
#Case 1: SUM() in SELECT statement
#Case 2: SUM() with a NULL result
#Case 3: SUM() with GROUP BY clause
#Case 4: SUM() with HAVING clause
#Case 5: SUM() with multiple expression