sql - How can I produce rows that sum subgroups of rows, without grouping? -

let's have table this:

id    income    expenses ------------------------- 1     $1000      $200 1     $2000      $400 2     $500       $200 2     $100       $60 

i'd add 1 row per id sums id's income , expenses. example:

id    income    expenses ------------------------- 1-sum $3000      $600 1     $1000      $200 1     $2000      $400 2-sum $600       $260 2     $500       $200 2     $100       $60 

is possible sql? read on , partition that'll add new column, not new row. might union best way go here?

using group by:

sqlfiddledemo

create table tab(id int , income int , expenses int);  insert tab(id, income, expenses) values (1, 1000, 200),(1, 2000, 400),(2, 500, 200),(2, 100, 60)   select id,        [income]         = sum(income),        [expenses]       = sum(expenses),        [grouping_level] = grouping_id(id, income, expenses) /* 0 - actual data , 3 - group based on column income, expenses*/ tab group grouping sets ((id), (id,income, expenses)) 

why grouping useful:

• using grouping sets gives great flexibility instance when want total sum use: group grouping sets ((id), (id,income, expenses), ())

• grouping sets can combined rollup/cube.

• grouping_id indicates grouping level. number works binary. grouping(a, b, c) if b c used grouping 011b => 3