Using Rollup in Oracle

Introduction

In this article we would see how to get the subtotal and grand total based on specified column in oracle. This would be very helpful in when you create any reports rather than doing in several steps we can do it by a single query.

Explanation

In that query we need to use Rollup function in Group By clause. In Rollup function we need to specify the column names. Based these columns query would return the subtotals.

And rollup would return Grand Total too at last.

Background

You must know at least about oracle and TOAD.

You need nothing but oracle and any editor like TOAD so on. I have used the TOAD editor for this.

Script for this,

Here I need to get the subtotal of UNRESTRICTED_QTY for each PRODUCT_NO,LOCATION_NO.

According to my query result was as shown below,

Result

In these results you can replace by your own word by using Grouping() function with columns which we are specified in Rollup function, in select clause as shown below.

But if you use Grouping() alone, it would return only either 1 or 0.

if that there is value for that record, then it would will return 0.

If there is no value for that record, then it would return 1.

Result

So Grouping() function alone returning either 0 or 1, you can replace those values by using case clause as shown below,

Result

Thank you

Murukan