RollUp is not workingRSS

• • Reply
  jsshivalik

  Member

  130 Points

  479 Posts

  RollUp is not working

  Apr 19, 2020 10:15 AM|jsshivalik|LINK

  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

• • Reply
  Sean Fang

  Participant

  1110 Points

  299 Posts

  Re: RollUp is not working

  Apr 20, 2020 02:35 AM|Sean Fang|LINK

  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