Oracle SUM

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:

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

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 the 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 the 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 the 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 of 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.