monthly totals and grand totals using grouping sets extension

Hello everyone,

Many users, when looking at a query report, wish that monthly totals and grand totals are also displayed. There are a number of ways on how this can be achieved, some of which are,

1) Copy and paste the query report to a spreadsheet and use the spreadsheet inbuilt function to do the monthly total.

2) Use the inbuilt SAP function by pressing the ctrl button on the keyboard and then right clicking your mouse over the column/s to be totaled. Thereafter, filter them month by month.

3) Use union all statement.

SELECT NULL, NULL, NULL, sum ( * ) AS total

FROM Customers

UNION ALL

SELECT City, State, NULL, sum ( * ) AS total

FROM Customers

GROUP BY City, State

UNION ALL

SELECT NULL, NULL, CompanyName, sum ( * ) AS total

FROM Customers

GROUP BY CompanyName;

However, it has two major drawbacks:

The query is quite lengthy.
The query is slow because SQL Server needs to execute all subqueries and combines the result sets into a single one.

4) Another lesser known method to get the totals and grand totals is by the use of GROUP BY extensions. The GROUP BY extensions consists of ROLLUP, CUBE and GROUPING SETS. These are used to solve the two major drawbacks that arise when using Union all statement to obtain subtotals and totals.

But first, what is GROUP BY clause?

The standard GROUP BY clause of a SELECT statement allows you to group rows in the result set according the grouping expressions you supply.

Data is aggregated for the columns provided by the GROUP BY clause. Important to note that the data will not be ordered in the GROUP BY columns and you need to explicitly order them by using the ORDER BY clause.

Important points for the GROUP BY SQL Statement:

The GROUP BY clause can only be used in a SQL SELECT statement.
The GROUP BY clause must be after the WHERE clause. (If one exists.)
The GROUP BY clause must be before the ORDER BY clause. (If one exists.)
To filter the GROUP BY results, you must use the HAVING clause after the GROUP BY.
The GROUP BY clause is often used in conjunction with an aggregate function such as COUNT, MIN, MAX, AVG, or SUM.
Each table or view column in any nonaggregate expression in the SELECT list must be included in the GROUP BY list
Except for TEXT, NTEXT, and IMAGE, any column can be called in the GROUP BY clause.

Traditionally, GROUP BY is used to aggregate data. However, ROLLUP, CUBE and GROUPING SETS extensions are used to return different aggregate values. ROLLUP will provide the aggregation in the provided order of the columns while CUBE will provide different combinations of the provided columns and GROUPING SETS has the option of aggregation in a customized manner. By using the GROUPING function, more options for the above features can be derived.

a) ROLLUP extension:

You can specify a hierarchy of grouping attributes using the ROLLUP clause.

A common requirement of many applications is to compute subtotals of the grouping attributes from left-to-right, in sequence. This pattern is referred to as a hierarchy because the introduction of additional subtotal calculations produces additional rows with finer granularity of detail.

GROUP BY ROLLUP creates a group for each combination of column expressions. In addition, it “rolls up” the results into subtotals and grand totals. To do this, it moves from right to left decreasing the number of column expressions over which it creates groups and the aggregation(s).

The column order affects the ROLLUP output and can affect the number of rows in the result set.

For example, GROUP BY ROLLUP (col1, col2) creates groups for each combination of column expressions in the following lists.

col1, col2
col1 NULL
NULL, NULL–This is the grand total

b) CUBE extension:

GROUP BY CUBE creates groups for all possible combinations of columns. For GROUP BY CUBE (col1, col2) the results have groups for unique values of

col1, col2
NULL, col2
col1, NULL
NULL, NULL–This is the grand total

c) GROUPING SETS extension:

The GROUP BY GROUPING SETS clause allows you to group your results multiple ways, without having to use multiple SELECT statements to do so.

Multiple groupings using GROUPING SETS:

SELECT City, State, CompanyName, COUNT( * ) AS Cnt

FROM Customers

WHERE State IN ( ‘MB’ , ‘KS’ )

GROUP BY GROUPING SETS( ( City, State ), ( CompanyName ) , ( ) );

GROUP BY GROUPING SETS ( )

If you use an empty GROUPING SETS specification ‘( )’ in the GROUP BY clause, this results in a grand total row for all things that are being totaled in the results. This is useful as one of the elements of a GROUPING SET.

Correct syntax

Grouping syntax is interpreted differently for a GROUP BY GROUPING SETS clause than it is for a simple GROUP BY clause. For example, GROUP BY (X, Y) returns results grouped by distinct combinations of X and Y values. However, GROUP BY GROUPING SETS (X, Y) specifies two individual grouping sets, and the result of the two groupings are unioned together. That is, results are grouped by (X), and then unioned to the same results grouped by (Y).

To avoid any ambiguity for complex expressions, use parentheses around each individual grouping set in the specification whenever there is a possibility for error. For example, while both of the following statements are correct and semantically equivalent, the second one reflects the recommended form:

SELECT * FROM t GROUP BY GROUPING SETS ( X, Y );

SELECT * FROM t GROUP BY GROUPING SETS( ( X ), ( Y ) );

Limitations when using GROUP BY clause

As you would expect, there are a few limitations when using the GROUP BY clause in your SELECT statement. Below is a list of the main limitations that you will need to be familiar with.

For GROUP BY clauses that do not contain ROLLUP, CUBE or GROUPING SETS:

The number of GROUP BY items is limited by the GROUP BY column size, aggregate values, and aggregated columns.

For GROUP BY clauses that contain ROLLUP, CUBE or GROUPING SETS:

The maximum number of expressions is 32.
The maximum number of groups is 4096.

For the purpose of this blog we shall be concentrating on the GROUPING SETS extension.

Example 1.

Consider the below query

SELECT ISNULL (T0.[TaxDate],DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,MAX(T0.[TaxDate]))+1,0)))
AS 'Date',T0.[DocNum] ,case when grouping ( T0.[CardName]) = 1 and grouping (T1.[Vatgroup]) = 0 
then 'SALES INVOICE MONTHLY TOTAL FOR' +' ' + T1.[Vatgroup] 
WHEN grouping ( T0.[CardName]) = 1 and grouping (T1.[Vatgroup]) =1 
then 'TOTAL SALES FOR THE PERIOD' ELSE T0.[CardName] END as 'Name',
T1.[Vatgroup] AS 'VATGROUP', SUM(T1.[TotalSumSy]) as 'Net',
sum(t1.[linevats]) as 'VAT', SUM(T1.[GTotal]) AS 'Gross',
grouping (T0.[TaxDate]) as 'grpdate', grouping (T0.[DocNum]) as
'grpdocnum',grouping ( T0.[CardName]) as 'grpname', grouping
(T1.[Vatgroup]) as 'grpvatgroup'

FROM OINV T0 INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry]

WHERE T0.[TaxDate] >=[%0] AND T0.[TaxDate] <=[%1]

GROUP BY GROUPING SETS ((T0.[DocNum] ,T1.[Vatgroup],T0.[CardName],
T0.[TaxDate]),(T1.[Vatgroup], MONTH(T0.[TaxDate]),
YEAR(T0.[TaxDate])), ())

UNION ALL

SELECT ISNULL (T0.[TaxDate],DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,MAX(T0.[TaxDate]))+1,0))),
T0.[DocNum], case when grouping ( T0.[CardName]) = 1 and grouping (T1.[Vatgroup]) = 0
then 'SALES CREDIT MEMO MONTHLY TOTAL FOR' +' ' + T1.[Vatgroup] 
WHEN grouping ( T0.[CardName]) = 1 and grouping (T1.[Vatgroup]) =1 then
'TOTAL CREDIT MEMOS' ELSE T0.[CardName] END as 'Name', T1.[Vatgroup],
SUM(T1.[TotalSumSy])*-1, sum(t1.[linevats])*-1, SUM(T1.[GTotal])*-1,grouping
(T0.[TaxDate]) as 'grpdate', grouping (T0.[DocNum]) as 'grpdocnum', 
grouping ( T0.[CardName]) as 'grpname', grouping (T1.[Vatgroup]) as 'grpvatgroup'

FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.[DocEntry] = T1.[DocEntry]

WHERE T0.[TaxDate] >=[%0] AND T0.[TaxDate] <=[%1]

GROUP BY GROUPING SETS ((T0.[DocNum] ,T1.[Vatgroup],T0.[CardName],
T0.[TaxDate]),(T1.[Vatgroup], MONTH(T0.[TaxDate]), YEAR(T0.[TaxDate])),())
ORDER BY DATE,grpvatgroup

In the above query,you will observe that we are using a grouping function.

The GROUPING function is used to allow us to check whether the specified column in the GROUP BY clause is aggregated or not. This function returns two values,0 and 1, where 0 represents the non aggregated result set while 1 represents the aggregated result set.

If we did not use a grouping function, then we cannot distinguish whether a NULL in a subtotal row is a placeholder NULL, or a NULL resulting from the evaluation of the underlying data for the row. As a result, it is also difficult to distinguish between a detail row, a subtotal row, and a grand total row.

We can therefore use the grouping functionality to give a more descriptive name to the subtotals/totals using case statements.

On executing the above query, you will notice that the monthly totals given by each union is separate from the other union. That is, the totals for the sales invoice will be separate from the totals for the credit memos monthly. For many, this will be fine.

Example 2.

Nevertheless, there will still be others who will be wanting the sales invoices and credit memos to be totaled together monthly.

So, is it possible to total across the Union all? The answer is yes, one can do so. What all you need to do is to change the above query in to a sub-query, like so

Declare @fromdate as datetime

Declare @Todate as datetime

set @fromdate = /* select min(Ta.[fromDate]) from osrt ta where Ta.[fromDate] >= */ [%0]

set @Todate = /* select max(Tb.[toDate]) from osrt tb where Tb.[toDate] <= */ [%1]

SELECT ISNULL (z.date,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,MAX(z.date))+1,0)))
as date, z.doc AS docno, case when grouping ( Z.NAME) = 1 and 
grouping (z.vatgroup) = 0 then 'SALES INVOICE MONTHLY TOTAL FOR' +' ' + z.vatgroup
WHEN grouping ( Z.NAME) = 1 and grouping (z.vatgroup) =1 then 
'TOTAL SALES FOR THE PERIOD' ELSE Z.NAME END as name, z.vatgroup,
sum(z.net) AS NET, SUM(Z.VAT) AS VAT, SUM(Z.GROSS) AS GROSS

FROM

((SELECT T0.[TaxDate] AS Date, T0.[DocNum] as doc, T0.[CardName] as Name,
T1.[VatGroup] AS VATGROUP, SUM(T1.[TotalSumSy]) as Net, sum(t1.[linevats])
as VAT, SUM(T1.[GTotal]) AS Gross

FROM OINV T0 INNER JOIN INV1 T1 ON T0.[DocEntry] = T1.[DocEntry]

WHERE T0.[TaxDate] >=[%0] AND T0.[TaxDate] <=[%1]

GROUP BY T0.[TaxDate], T0.[DocNum],T0.[CardName],T1.[VatGroup])

UNION ALL

(SELECT T0.[TaxDate] AS Date, T0.[DocNum] as doc, T0.[CardName] as Name,
T1.[VatGroup] AS VATGROUP, SUM(T1.[TotalSumSy])*-1 as Net, 
sum(t1.[linevats])*-1 AS VAT , SUM(T1.[GTotal])*-1 AS GROSS

FROM ORIN T0 INNER JOIN RIN1 T1 ON T0.[DocEntry] = T1.[DocEntry]

WHERE T0.[TaxDate] >=[%0] AND T0.[TaxDate] <=[%1]

GROUP BY T0.[TaxDate], T0.[DocNum],T0.[CardName],T1.[VatGroup] )) Z

GROUP BY GROUPING SETS ((z.doc,z.vatgroup,z.name,z.date),
(z.vatgroup,MONTH(z.date),YEAR(z.date)),()) order by date

Summary:

The ROLLUP and CUBE clauses can be considered shortcuts for predefined GROUPING SETS specifications.

ROLLUP is equivalent to specifying a series of grouping set specifications starting with the empty grouping set ‘()’ and successively followed by grouping sets where one additional expression is concatenated to the previous one. For example, if you have three grouping expressions, a, b, and c, and you specify ROLLUP, it is as though you specified a GROUPING SETS clause with the sets: (), (a), (a, b), and (a, b, c ). This construction is sometimes referred to as hierarchical groupings.

CUBE offers even more groupings. Specifying CUBE is equivalent to specifying all possible GROUPING SETS. For example, if you have the same three grouping expressions, a, b, and c, and you specify CUBE, it is as though you specified a GROUPING SETS clause with the sets: (), (a), (a, b), (a, c), (b), (b, c), (c), and (a, b, c ).

Kindly share your thoughts and comments on the use of group by extensions to obtain totals and grand totals in the comment section below. Your feedback will be highly appreciated.

If you would like to know more about SQL in general, then I suggest you visit

https://answers.sap.com/tags/122888716930844301706258287775555

https://blogs.sap.com/tags/122888716930844301706258287775555

Stay tuned for further blogs in my profile

mk shah

With regards,

m.k. shah

SAP B1 v 9.2.

References/Acknowledgements:

https://help.sap.com/docs/SAP_SQL_Anywhere/e38b2f6217f24bdb90a3ff8ae57b1dd5/819834056ce21014809ac64122d0e9db.html?q=grouping%20sets

https://www.sap-business-one-tips.com/how-to-display-total-for-a-column-in-query-manager-sap-business-one/

https://learn.microsoft.com/en-us/sql/t-sql/queries/select-group-by-transact-sql?view=sql-server-ver16