SQL SUM function without grouping data

Question

I need to do a sum of a variable broken by certain other variables. I normally would do this with the group by function.

However in this case, I do not want to roll-up the data. I want to keep the original data with some sort of aggregated sum.

-ID-- --amount--
  1        23
  1        11
  1        8
  1        7
  2        10
  2        20
  2        15
  2        10

Result

-ID-- --amount-----SUM
  1        23      49
  1        11      49
  1        8       49
  1        7       49
  2        10      55
  2        20      55
  2        15      55
  2        10      55

Answer 1

You could use a subquery to get the total for each id and join that back to your table:

select t.id, 
  t.amount, 
  t1.total
from yt t
inner join 
(
  select id, sum(amount) total
  from yt
  group by id
) t1
  on t.id = t1.id;

See SQL Fiddle with Demo