# 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

Sunday, April 19, 2020 10:15 AM

### All replies

• 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: 