问题描述

我正在尝试将多列中重复行(具有相同 ID、月份和人员)的值相加到第一个或最后一个重复行.然后删除重复的行，除了具有总值的那一行.最大的问题是有时我需要对两个不同列中的值求和.

I'm trying to sum values from duplicates rows (with the same ID, Month and Person) in multiple columns to the first or the last duplicate row. Then delete the duplicate rows exept the one with the total value. The biggest problem is that sometimes I need to sum values in two different columns.

主表:

  ID Month Person  Value1 Value2
**123  1   Smith**   10     20  
**123  1   Smith**   5      NULL
**123  1   Smith**   NULL   5
  123  2   Smith     10     5
**189  3   Murphy**  NULL   15 
**189  3   Murphy**  NULL   10 
  190  2   Brown     25     25
**345  2   Lee**     25     20 
**345  2   Lee**     25     20 

Result1(求和后的预期结果将值复制到第一个):

Result1 (expected result after sum duplicates values to the first one):

ID Month Person Value1 Value2
123  1    Smith **15** **25** 
123  1    Smith   5      NULL
123  1    Smith   NULL   5
123  2    Smith   10     5 
189  3    Murphy  NULL **25** 
189  3    Murphy  NULL   10
190  2    Brown   25     25
345  2    Lee   **50** **40**
345  2    Lee     25     20 

FinalTable(删除重复项后的预期结果，第一个除外):

FinalTable (expected result after deleting duplicates, except the first one):

ID Month Person Value1 Value2
123  1    Smith **15** **25** 
123  2    Smith   10     5 
189  3    Murphy  NULL **25** 
190  2    Brown   25     25
345  2    Lee   **50** **40** 

我正在尝试使用此代码:

I'm trying with this code:

SELECT ID, Month, Person, SUM(Value1), SumValue2
FROM
(
    SELECT ID, Month, Person, Value1, SUM(Value2) AS SumValue2
    FROM db.Hours
    GROUP BY ID, Month, Person, Value1
 )
 GROUP BY ID, Month, Person, SumValue2

但有时它是 Value2 总和的两倍.

But sometimes it makes double sum of total of Value2.

推荐答案

SELECT ID, Month, Person, SUM(Value1) as SumValue1, SUM(Value2) AS SumValue2
FROM db.Hours
GROUP BY ID, Month, Person

我不确定您为什么将其视为两个步骤等.没有删除重复项等.这是按聚合分组的场景.您将行分组并汇总值列的位置.您需要将其设为多步操作的唯一原因是您的值列之一是否会在您的分组中被考虑，例如ID、月、人和值 1.在您的情况下，您只需按 ID、月份、人员分组，并对 Value1 和 Value2 进行聚合.

I am not sure why you are looking at this as two steps etc. There is no removal of duplicates etc. this is a scenario for Group By Aggregation. Where you group like rows and summarize the value columns. The only reason you would need to make this a multi step operation would be if one of your value columns will be considered within your grouping e.g. ID, Month, Person, and Value1. In your case you simply need to group by ID, Month, Person and do the aggregation for Value1 and Value2.

这篇关于sql sum 在多列中重复的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！