**Summary**: in this tutorial, you will learn how to use the Oracle `SUM()`

function to calculate the sum of all or distinct values in a set.

## Oracle `SUM()`

function syntax

The Oracle `SUM()`

function is an aggregate function that returns the sum of all or distinct values in a set of values.

The following illustrates the syntax of the Oracle `SUM()`

function:

Code language: SQL (Structured Query Language) (sql)`SUM( [ALL | DISTINCT] expression)`

The Oracle `SUM()`

function accepts a clause which can be either `DISTINCT`

or `ALL`

.

- The
`DISTINCT`

clause forces the`SUM()`

function to calculate the sum of unique values. - The
`ALL`

clause causes the`SUM()`

function to calculate the sum of all values, including duplicates.

For example, the sum of `DISTINCT`

of 1, 1, and 2 is 3, while the sum of `ALL`

of 1, 1, and 3 is 4.

If you omit the clause, the `SUM()`

function will use the `ALL`

clause by default.

Note that the `SUM()`

function ignores NULL values.

## Oracle `SUM()`

function examples

We will use the `order_items`

table in the sample database for the demonstration.

### A) Simple Oracle `SUM()`

function example

The following statement returns the sum of quantity of products placed by customers:

```
SELECT
SUM( quantity )
FROM
order_items;
```

Code language: SQL (Structured Query Language) (sql)

### B) Oracle `SUM()`

with `GROUP BY`

clause

To get the sum of quantity of products on sales order by product, you use the following statement:

```
SELECT
product_id,
SUM( quantity )
FROM
order_items
GROUP BY
product_id
ORDER BY
SUM( quantity ) DESC;
```

Code language: SQL (Structured Query Language) (sql)

In this example,

- First, the
`GROUP BY`

clause groups the rows in the`order_items`

into groups by product id (`product_id`

). - Second, the
`SUM()`

function returns the sum of quantity for each group.

Similarly, you can use the `SUM()`

function with the `GROUP BY`

clause to retrieve the sales orders and their total values:

```
SELECT
order_id,
SUM( quantity * unit_price ) order_total
FROM
order_items
GROUP BY
order_id
ORDER BY
order_total;
```

Code language: SQL (Structured Query Language) (sql)

In this example,

- First, the formula
`quantity * unit_price`

returns the value of each order item. - Second, the
`GROUP BY`

clause divides the`order_items`

into groups by orders (`order_id`

). - Third, the
`SUM()`

function returns the sum all order items for each order.

### C) Oracle `SUM()`

with `HAVING`

example

The following statement returns the orders whose total values are between 1000 and 20,000:

```
SELECT
order_id,
SUM( quantity * unit_price ) order_total
FROM
order_items
GROUP BY
order_id
HAVING
SUM( quantity * unit_price ) BETWEEN 1000 AND 20000
ORDER BY
order_total DESC;
```

Code language: SQL (Structured Query Language) (sql)

In this example, we added a `HAVING`

clause to query in the previous example to select only orders whose total values are between 1000 and 20000.

### D) Oracle `SUM()`

with `INNER JOIN`

clause example

The following statement returns the products and their sales:

```
SELECT
product_name,
SUM( quantity * unit_price ) sales
FROM
order_items
INNER JOIN products
USING(product_id)
GROUP BY
product_name
ORDER BY
sales DESC;
```

Code language: SQL (Structured Query Language) (sql)

Because the `order_items`

table only stores the `product_id`

, we have to join it with the `products`

table to get the product names.

In this tutorial, you have learned how to use the Oracle `SUM()`

function to calculate the sum of all or unique values in a set of values.