silikonkm.blogg.se - Rowwise grandtotal in oracle119

ROWWISE GRANDTOTAL IN ORACLE119 HOW TO
ROWWISE GRANDTOTAL IN ORACLE119 CODE
ROWWISE GRANDTOTAL IN ORACLE119 PLUS

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. In the below query we added a column which sums up all the days count and displayed as a new column. 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 ORACLE119 HOW TO

The number of rows in the output is derived from the number of unique combinations of values in the grouping columns.In this post we will see how to calculate row wise Grand total and column wise Grand total along with PIVOT. In the syntax above, the ROLLUP clause generates the following grouping sets: In other words, if you have n columns listed in the ROLLUP, you will get n+ 1 level of subtotals with ROLLUP.

ROWWISE GRANDTOTAL IN ORACLE119 PLUS

The ROLLUP clause generates the number of grouping sets which is the same as the number grouping columns specified in the ROLLUP plus a grand total.

Then, progressively create higher-level subtotals of the grouping columns, which are col2 and col1 columns, from right to left.

First, calculate the standard aggregate values in the GROUP BY clause.

In the query syntax above, the ROLLUP creates subtotals that roll up from the most detailed level to a grand total, following a grouping column specified in the ROLLUP.

ROWWISE GRANDTOTAL IN ORACLE119 CODE

Code language: SQL (Structured Query Language) ( sql )