ROLLUP function: How to get sub total row in a Oracle SQL query as output

 In oracle SQL query when you query for any data, the output is table of columns and rows selected. But there is not total added at the bottom.

How to add a total at the bottom and also how to add a sub-total for each change of value in a specific column.

select fruits, city, sum(price) from fruits_table group by rollup (fruits,city)

In the above example the table a total will be added to FRUITS level and also a sub-total will be added at each change in CITY.

However, there is catch. If there is more than one dimension (in the above example FRUITS & CITY) then you should sort the table by CITY and Fruits so that the sub-totals appear in order. Below the updated version of the example query with ordering.

select fruits, city, sum(price) from fruits_table group by rollup (fruits,city) order by city,fruits

Thanks for visiting. If you have any questions reach out to me directly at askme.kkhelps@gmail.com