sql server - Data rows plus totals row in a single scan -

imagine have following data in table:

groupname  volume  class  mark ---------- ------- ------ ---- group1     50      1      o group1     50      1      o group1     50      1      x group1     25      2      o group2     25      1      x group2     17      3      x group2     11      2      o group3     11      1      o group3     19      3      x 

and there need add totals row @ end (sum volume , null rest of columns).

i know need can achieved adding total union all as:

select 0 istotal, groupname, class, mark, volume datatable union select 1, null, null, null, sum(volume) datatable order istotal, groupname, class 

but way table scanned twice.

to avoid scanning data twice, tried using group by:

select grouping(groupname) istotal, groupname, class, mark, sum(volume) volume datatable group grouping sets ((), (groupname, class, mark, volume)) order istotal, groupname, class 

this way there 1 table scan, , i'm getting need, except 2 first rows of sample data (which duplicates) merged one, , need duplicates kept separate rows.

the question: possible table data totals row added table data scanned once , duplicates kept separate rows?

the desired result returned union all query:

istotal groupname   class  mark volume ------- ----------- ------ ---- ------- 0       group 1     1      o    50 0       group 1     1      o    50 0       group 1     1      x    50 0       group 1     2      o    25 0       group 2     1      x    25 0       group 2     2      o    11 0       group 2     3      x    17 0       group 3     1      o    11 0       group 3     3      x    19 1       null        null   null 258 

the result returned group grouping sets query:

istotal groupname  class  mark volume ------- ---------- ------ ---- ------- 0       group 1    1      o    100 0       group 1    1      x    50 0       group 1    2      o    25 0       group 2    1      x    25 0       group 2    2      o    11 0       group 2    3      x    17 0       group 3    1      o    11 0       group 3    3      x    19 1       null       null   null 258 

even thought have duplicated rows, can make them unique , fix issue. 1 way using row_number function.

for example:

declare @datasource table (     [groupname] varchar(6)    ,[volume] tinyint    ,[class] tinyint    ,[mark] char(1) );  insert @datasource ([groupname], [volume], [class], [mark]) values ('group1', '50', '1', 'x')       ,('group1', '50', '1', 'x')       ,('group1', '50', '1', 'o')       ,('group1', '25', '2', 'o')       ,('group2', '25', '1', 'x')       ,('group2', '17', '3', 'x')       ,('group2', '11', '2', 'o')       ,('group3', '11', '1', 'o')       ,('group3', '19', '3', 'x');  datasource ([rowid], [groupname], [volume], [class], [mark]) (     select row_number() over(order (select 1))           ,[groupname]           ,[volume]           ,[class]           ,[mark]     @datasource ) select grouping([groupname]) [istotal]       ,[groupname]       ,[class]       ,[mark]       ,sum([volume]) [volume] datasource group grouping sets ((), ([rowid], [groupname], [volume], [class], [mark])) order [istotal]         ,[groupname]         ,[class]; 

will give you:

exactly same initial query:

select 0 istotal, groupname, class, mark, volume @datasource union select 1, null, null, null, sum(volume) @datasource order istotal, groupname, class 

if compare execution plans can see 1 table scan performed: