Related Articles

The "Sum" function in Oracle allows you to add up all data from a specified column and displays a result in your query results. You can use the multiple feature with the sum function to multiply the results from the sum function. You use this feature in complex queries where you need to return several mathematical results to the user.

Purpose

The purpose of the sum function is to add up several rows of data quickly using the Oracle database engine. The sum function is quick, so you do not manually add up values in a loop structure, which slows down your database server. Using the sum function and the multiply feature together, you can obtain two values, the sum and the multiplied product, without creating two queries for two values.

Sum Function

The sum function is used in your "select" query. You must specify a column you want to add up in the sum function. The following code adds up all customer orders and displays the result: select sum(order_total) as Total from orders where customerid=22 The results from the above query show the total amount for customer ID 22.

Multiply

After you write the sum function, you can multiply by a specified number or use another column for the multiplication equation. For instance, the following code multiplies the total by the number of orders produced by the customer: select sum(order_total) as Total, sub(order_total)*count(orderid) from orders where customerid=22 The query above shows both results, but you can combine the two equations into one, which produces one value in the results.

Considerations

If you need to multiply individual records, you must create queries for each record. You can also create a cursor in Oracle to loop through a number of records and record each result in a variable. Cursors are slower in Oracle, but they are used in cases where you only have one option to return results for several records individually.

About the Author

Jim Campbell has been a computer engineer for over five years. He excels in hardware repair, computer programming and troubleshooting, and software design. He is currently attending Florida Atlantic University, pursuing a master's degree in computer and electrical engineering and fine-tuning his technical writing abilities.