SQL SUM函数，不对数据进行分组 [英] SQL SUM function without grouping data

问题描述

我需要对被某些其他变量破坏的变量求和.我通常会使用group by函数执行此操作.

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

但是，在这种情况下，我不想汇总数据.我想使用某种聚合的sum保留原始数据.

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

结果

-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

推荐答案

您可以使用子查询来获取每个id的总数，然后将其联接回到表中:

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;

请参见带有演示的SQL提琴

这篇关于SQL SUM函数，不对数据进行分组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！