根据条件获取列的总和 [英] Get Sum for the column based on a condition

问题描述

我正在处理SQL查询.查询如下:

I am working on a SQL Query. Query is something like below:

Select 
    a.field1,
    b.field2,
    c.field3,
    c.field4,
    b.filed5,
    a.field6,
    d.field7 
from 
    a 
    Inner join b on a.field1 = b.field1 
    right join c on b.field2 = c.field3 
    left  join d on d.filed3 = a.field1 
where some conditions;

上面的输出将是这样的:

The output of the above would be something like this:

field1 | field2 | field3 | field4 | field5 | field6 | field7
--------------------------------------------------------------
name   | value1 | other1 | 1      | diff   |   new  | 100
name1  | value2 | other2 | 1      | diff1  |   new1 | 100
name2  | value3 | other3 | 2      | diff2  |   new2 | 100

因此，我需要在结果中添加一个新列，该列基于field4值(如果它们相同)对field7求和.

So i need a new column in the result which sums the field7 based on field4 value(if they are same).

在SQL中可能吗?我尝试在此处使用Group by field 4，但出现错误，请在分组依据中使用field1.因此我无法获得预期的结果.

Is it in possible in SQL? I tried to use Group by field 4 here but I am getting an error that field1 should be used in group by. So i am not able get the result as expected.

预期结果:

field1 | field2 | field3 | field4 | field5 | field6 | field7
--------------------------------------------------------------
name   | value1 | other1 | 1      | diff   |   new  | 200
some   | value3 | other3 | 2      | diff2  |   new2 | 100

基本上我想根据条件删除一列并求和最后一个字段.

Basically i want to remove one column based on a condition and sum the last field.

任何建议都是有帮助的.

Any suggestions are helpful.

推荐答案

要计算具有相同field4的记录组中的field7之和，可以使用窗口函数SUM(...) OVER(...).

To compute the sum of field7 in groups of records having the same field4, you can use window function SUM(...) OVER(...).

要只为每组具有相同field4的记录保留一条记录，可以在内部查询中使用ROW_NUMBER()并在外部查询中过滤掉不需要的记录.请注意，您需要排序条件才能可靠地选择应在每个组中保留的记录.我选择使用field1(根据需要进行更改):

To just keep one record per group of records having the same field4, you can use ROW_NUMBER() in an inner query and filter out unwanted records in the outer query. Please note that you need a sort criteria to reliably choose which record should be kept in each group. I chose to use field1 (change it as you need):

您的(伪)查询应类似于:

Your (pseudo-)query should look like:

SELECT * FROM (
    SELECT 
        a.field1,
        b.field2,
        c.field3,
        c.field4,
        b.filed5,
        a.field6,
        SUM(d.field7) OVER(PARTITION BY c.field4) sm,
        ROW_NUMBER()  OVER(PARTITION BY c.field4 ORDER BY a.field1) rn,
    FROM
        a 
        INNER JOIN b on a.field1 = b.field1 
        RIGHT JOIN c on b.field2 = c.field3 
        LEFT  JOIN d on d.field3 = a.field1 
    WHERE some conditions
) x
WHERE rn = 1

提示:您可以独立运行内部查询以查看其返回的内容(这有助于理解逻辑).

Hint: you can run the inner query independently to see what it returns (this helps understanding the logic).

这篇关于根据条件获取列的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！