Re: SQL function to return the grand total number of elements found

GROUP BY GROUPING SETS allows you to specify the exact desired levels of
sub-totals and/or grand-totals that you desire. I highly recommend getting
proficient with it since it provides more flexibility than CUBE and ROLLUP.

CUBE and ROLLUP are great too, but they don't provide the level of
flexibility that GROUP BY GROUPING SETS provides. CUBE and ROLLUP are the
better choice if you want all the totals they are designed to provide.
GROUP BY GROUPING SETS is the better choice when you don't want all the
totals.

Mike

date: Thu, 5 Jan 2017 20:20:29 +0000
from: "Steinmetz, Paul" <PSteinmetz@xxxxxxxxxx>
subject: RE: SQL function to return the grand total number of elements
found

Rob,

I had those results, but could not get a grand total.

Found this in SQL reference manual.

grand-total
Both CUBE and ROLLUP return a row which is the overall (grand total)
aggregation. This may be separately specified with empty parentheses within
the GROUPING SETS clause. It may also be specified directly in the GROUP
BY clause, although there is no effect on the result of the query. Example
C4
uses the grand-total syntax.

Example C4:
If you run the same query as Example C3 only replace ROLLUP with CUBE, you
can see additional grouping sets for (WEEK, SALES_PERSON), (DAY_WEEK,
SALES_PERSON), (DAY_WEEK), and (SALES_PERSON) in the result.
SELECT WEEK(SALES_DATE) AS WEEK,
DAYOFWEEK(SALES_DATE) AS DAY_WEEK,
SALES_PERSON,
SUM(SALES) AS UNITS_SOLD
FROM SALES
WHERE WEEK(SALES_DATE) = 13
GROUP BY CUBE( WEEK(SALES_DATE), DAYOFWEEK(SALES_DATE), SALES_PERSON )
ORDER BY WEEK, DAY_WEEK, SALES_PERSON

Still no luck.

Paul