将多行的值汇总为一行

In SQL Server 2012, I have a table my_table that has columns state, month, ID, and sales.

在SQL Server 2012中，我有一个表my_table，其中包含列state，month，ID和sales。

My goal is to merge different rows that have the same state, month, ID into one row while summing the sales column of these selected rows into the merged row.

我的目标是将具有相同状态，月份，ID的不同行合并为一行，同时将这些选定行的销售列汇总到合并行中。

For example:

例如：

state    month    ID    sales
-------------------------------
FL       June     0001   12,000
FL       June     0001    6,000
FL       June     0001    3,000
FL       July     0001    6,000
FL       July     0001    4,000  
TX       January  0050    1,000
MI       April    0032    5,000
MI       April    0032    8,000
CA       April    0032    2,000

This what I am supposed to get

这就是我应该得到的

state    month    ID    sales
-------------------------------
FL       June     0001   21,000
FL       July     0001   10,000  
TX       January  0050    1,000
MI       April    0032   13,000
CA       April    0032    2,000

I did some research, and I found that the self join is supposed to do something similar to what I am supposed to get.

我做了一些研究，我发现自我加入应该做类似于我应该得到的东西。

2 个解决方案

#1

Unless I am missing something in the requirements, why not just use an aggregate function with a GROUP BY:

除非我遗漏了需求中的某些内容，否则为什么不使用GROUP BY的聚合函数：

select state, month, id, sum(sales) Total
from yourtable
group by state, month, id
order by id

See SQL Fiddle with Demo

请参阅SQL Fiddle with Demo

The result is:

结果是：

| STATE |   MONTH | ID | TOTAL |
--------------------------------
|    FL |    July |  1 | 10000 |
|    FL |    June |  1 | 21000 |
|    CA |   April | 32 |  2000 |
|    MI |   April | 32 | 13000 |
|    TX | January | 50 |  1000 |

#2

Considering there should be an index on column id, this query would be a better solution:

考虑到列id应该有一个索引，这个查询将是一个更好的解决方案：

select state, month, id, sum(sales) Total
from yourtable
group by id, state, month
order by id

#1