对SQL Server中特定ID值的总和

Say, I have 3 columns in my table :

说,我的表中有3列:

---------------
name                 | ID  | Amt
ABC                  |  1  | 500
ABC                  |  1  | 650
XYZ                  |  2  | 700
XYZ                  |  2  | 550
DEF                  |  3  | 200

how can I get an output where the amount is aggregated for each ID, i.e., ABC gives 1150, XYZ 1250 and DEF 200?

如何获得每个ID汇总金额的输出,即ABC给出1150,XYZ 1250和DEF 200?

3 个解决方案

#1

You want a group by, with what sounds like a sum from your example:

你想要一个组,听起来像你的例子中的总和:

Select Name, ID, sum(amt) as TotalAmt
from MyTable
Group by Name, ID

#2

SELECT name,SUM(Amt) FROM [table] GROUP BY name

or, if you want to display name and ID

或者,如果要显示名称和ID

SELECT name,ID,SUM(Amt) OVER (PARTITION BY name) FROM [table]

#3

This could be done using a simple group by statement:

这可以使用简单的group by语句来完成:

select name,
       id,
       sum(amt) as agg_sum
from total
group by name,id;

SQL Fiddle Demo

SQL小提琴演示

#1