Cloud

sum

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 supported input and return types are listed in the following table.

Input type Return type

int

LONG

LONG

LONG

FLOAT

DOUBLE

DOUBLE

DOUBLE

interval

interval

If the input type is 32-bit, then the result is 64-bit.

Examples

The following two sample tables are used in these examples:

customer table

CREATE TABLE customer (
  customer_id int,
  cust_name text
);
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;

This creates the following table:

+-------------+-----------+
| 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 text,
    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;

The rental table 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        |
+------------+---------------------+---------------------+-----------+---------------+-------------+

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

This returns a sum value of the total_price:

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

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

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;

This calculates the total_price from a group of customer_id:

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

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;

This returns the customers who spent greater than or equal to 3000:

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

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 output displays 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  |
+------------+-------------------+