#Rowwise grandtotal in oracle11g how to#

SUM ( Thursday ), SUM ( Friday ), SUM ( Saturday ), SUM ( Sunday ), SUM ( EventCategoryTotal ) FROM # tmpResult SUM ( Monday ), SUM ( Tuesday ), SUM ( Wednesday ), UNION ALL /* Column wise Grand Total through aggregate function*/ SELECT 'Total' , SUM ( CntDays ) FOR IN ( Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday ) ) AS PRР”В®ventCategory, Monday, Tuesday, Wednesday, Thursday ,Р”Р‡riday, Saturday, Sunday, EventCategoryTotal SUM ( CntDays ) OVER ( Partition By EventCategory ) EventCategoryTotal Sunday, 0 ) AS Sunday ,Р”Р‡ROM ( SELECT EventCategory, WeekDays, CntDays, /* Row wise Grand Total through aggregate function*/ Saturday, 0 ) AS Saturday, ISNULL ( PR. ) -SELECT * FROM CteBaseResult SELECT PR. ), CteBaseResult ( EventCategory, WeekDays, CntDays ) AS ( SELECT EventCategory, WeekDays, Count (*) AS CntDays WITH CTEEvent ( EventCategory, WeekDays ) AS ( SELECT EventCategory, DateName ( WEEKDAY, EventDate ) As WeekDays Here aggregate function with PartitionBY is used to retrieve the row wise Grand Total and also you can see the base query is built upon CTE before applying the PIVOT. In order to get the column wise total, the result set of PIVOT is first dumped into the temp table and then applied UNION ALL to retrieve the SUM of each column.Р”Р„lso note that the approach for the row wise grand total is also changed just to give you the alternate options available to populate the grand total. * ROW WISE GRAND TOTAL*/ FROM ( /* Base Result */ SELECT EventCategory, DATENAME ( WEEKDAY, EventDate ) As WeekDaysР”В¬OUNT ( WeekDays ) FOR IN ( Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday ) ) AS PR

Sunday, 0 ) AS Sunday, /* ROW WISE GRAND TOTAL*/ Thursday, 0 ) AS Thursday, ISNULL ( PR. Wednesday, 0 ) AS Wednesday, ISNULL ( PR. Create table Employee1 as Select from Employee Query 2: Create the table structure duplicate to another table. Query 1: Create the duplicate table with data. The following are 2 most important queries which are used in 90 of Real Time Scenarios in SQL. In the below query we added a column which sums up all the days count and displayed as a new column. There are some needs where user needs to create the structure of the table. The easiest way to apply row wise grand total is summing up all the aggregate fields on the result set. You can use the script from the post Example of PIVOTР’В to create and populate the base table data.Р”Р‡rom the previous articles we came to understand the result of the PIVOT is a result set which can be used further to manipulate the data.

If you are naР“Р‡ve to PIVOT, have a look at the other posts to understand the basics of it before going through this post.

#Rowwise grandtotal in oracle11g how to#

In this post we will see how to calculate row wise Grand total and column wise Grand total along with PIVOT.