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.

order_items table

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)
Oracle SUM - Example

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)
Oracle SUM - GROUP BY example

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)
Oracle SUM - expression and group by example

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)
Oracle SUM - HAVING clause example

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)
Oracle SUM - Inner Join example

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.

Was this tutorial helpful?