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.
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA