RollUp is not working

Question

User-797751191 posted

Hi

  I have below code . It is not displaying GrandTotal . Secondly 1 record is appearing 2 times

SELECT Coalesce(CAST(T1.[DocNum] as varchar),'Grand Total') as DocNum,
T1.[DocDate], T1.[CardCode], T1.[CardName],
sum(T0.Quantity) as Quantity  FROM Test0 T0 INNER JOIN Test1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T1.[DocDate] >= '2020/04/01' and T1.[DocDate] <= '2020/04/15'
group by 
T1.DocNum,T1.[DocDate], T1.[CardCode], T1.[CardName],
RollUp(T1.DocNum)

Thanks

Answer 1

User-1330468790 posted

Hi jsshivalik,

 

I think you wrongly used RollUp(). This function will assume a hierarchy among the dimension columns and only returns grouping sets based on this hierarchy.

For example, RollUp(a,b,c) will assume the hierarchy a > b > c and return the sets as follow :

(a,b,c) 
(a,b)
(a)
() // this is used to generate "Grand Total"

The relationships among different sets are union. That means the select result of these sets will be concatenated to one table.

 

Before providing the solution, I would like to introduce a sub-clause of GROUP BY => GROUPING SETS.

GROUPING SETS((a,b,c),()) will returns you below two sets

(a,b,c)
() // this is used to generate "Grand Total"

 

Solution:

Therefore, I suggest you use GROUPING SETS to manually set the sets

SELECT Coalesce(CAST(T1.[DocNum] as varchar),'Grand Total') as DocNum,
Coalesce(CAST(T1.[DocDate] as varchar),''), Coalesce(CAST(T1.[CardCode] as varchar),''), Coalesce(CAST(T1.[CardName] as varchar),''),
sum(T0.Quantity) as Quantity  FROM Test0 T0 INNER JOIN Test1 T1 ON T0.DocEntry = T1.DocEntry
WHERE T1.[DocDate] >= '2020/04/01' and T1.[DocDate] <= '2020/04/15'
group by 
GROUPING SETS((T1.DocNum,T1.[DocDate],T1.[CardCode], T1.[CardName]),())

Test0 Data:

Test1 Data:

Result:

 

 

Hope this can help you.

Best regards,

Sean