将多行中的值汇总为一行 [英] Sum values from multiple rows into one row

问题描述

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

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

我的目标是将具有相同 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.

例如:

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

这是我应该得到的

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

我做了一些研究，我发现 self join 应该做一些类似于我应该得到的东西.

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

推荐答案

除非我在需求中遗漏了一些东西，为什么不使用带有 GROUP BY 的聚合函数:

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

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

参见SQL Fiddle with Demo

结果是:

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

这篇关于将多行中的值汇总为一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！