> ## Documentation Index > Fetch the complete documentation index at: https://docs.oxla.com/llms.txt > Use this file to discover all available pages before exploring further. # SUM ## 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 type | Return type | | ---------- | ----------- | | INT | LONG | | LONG | LONG | | FLOAT | DOUBLE | | DOUBLE | DOUBLE | | INTERVAL | INTERVAL | If the input type is 32-bit, then the result will be 64-bit. ## Examples We have two sample tables here: **customer table** ```sql theme={null} 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'); ``` ```sql theme={null} SELECT * FROM customer; ``` It will create a table as shown below: ```sql theme={null} +-------------+-----------+ | customer_id | cust_name | +-------------+-----------+ | 11112 | Alex | | 11113 | Aaron | | 11114 | Alice | | 11115 | Nina | | 11116 | Rosy | | 11117 | Martha | | 11118 | John | +-------------+-----------+ ``` **rental table** ```sql theme={null} 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); ``` ```sql theme={null} SELECT * FROM rental; ``` Here, we have a rental table which stores the details for car rental: ```sql theme={null} +------------+---------------------+---------------------+-----------+---------------+-------------+ | 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`: ```sql theme={null} SELECT SUM (total_price) AS total FROM rental ``` It will return a sum value of the `total_price`: ```sql theme={null} +--------+ | 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.` ```sql theme={null} 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`. ```sql theme={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: ```sql theme={null} 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: ```sql theme={null} +--------------+--------------+ | 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: ```sql theme={null} 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: ```sql theme={null} +--------------+--------------+ | 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. ```sql theme={null} 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. ```sql theme={null} +------------+-------------------+ | 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 | +------------+-------------------+ ```