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)
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
.NET forums are moving to a new home on Microsoft Q&A, we encourage you to go to Microsoft Q&A for .NET for posting new questions and get involved today.
Member
140 Points
518 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
Thanks
Contributor
2830 Points
838 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 :
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
Solution:
Therefore, I suggest you use GROUPING SETS to manually set the sets
Test0 Data:
Test1 Data:
Result:
Hope this can help you.
Best regards,
Sean