SELECT fvendno, fcompany, POItemKey, PORelsdate, SUM(totstdcost) AS TotStdCost
FROM #mytable
GROUP BY fvendno, fcompany, GROUPING SETS( (POItemKey, PORelsdate, ID), () )
;This only requires 1 scan, and 2 logical reads whereas the UNION approach requires 3 scans and 6 logical reads (2/4 if you exclude the Grand Total).
If you want to add a grand total, you can use the following grouping sets.
SELECT fvendno, fcompany, POItemKey, PORelsdate, SUM(totstdcost) AS TotStdCost, GROUPING(fvendno)
FROM #mytable
GROUP BY GROUPING SETS( (fvendno, fcompany, POItemKey, PORelsdate, ID), (fvendno, fcompany), () )
;
Drew
Edited: Added the query for a grand total.
Can't wait to try it. Hope to get to it today. Thank you in advance.
Drew, Thanks, This is awesome!!!
Is there a way to get the whole total at the end?
Please use GROUP BY Lookup / CUBE for your requirements.
I'd found these in SQL Server Central forum before,
SELECT coalesce (department, 'All Departments') AS Department,
coalesce (gender,'All Genders') AS Gender,
sum(salary) as Salary_Sum
FROM employee
GROUP BY ROLLUP (department,gender)
SELECT coalesce (department, 'All Departments') AS Department,
coalesce (gender,'All Genders') AS Gender,
sum(salary) as Salary_Sum
FROM employee
GROUP BY CUBE (department,gender)