Home Forums SQL Server 2016 SQL Server 2016 - Development and T-SQL Row inserts for groups of rows (Totals for groups of like data). RE: Row inserts for groups of rows (Totals for groups of like data).
January 4, 2018 at 7:19 am
bswhipp - Thursday, January 4, 2018 5:57 AMdrew.allen - Wednesday, January 3, 2018 9:06 AMSELECT 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!!!