# Source: https://docs.oxla.com/sql-reference/sql-functions/window-functions/sum.md

# Source: https://docs.oxla.com/sql-reference/sql-functions/aggregate-functions/sum.md

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

<Note>If the input type is 32-bit, then the result will be 64-bit.</Note>

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