根据其他联接表列计算字段的总和 [英] Calculating the sum of a field based on other join table columns

问题描述

我正在处理这样的SQL查询:

I am working on a SQL query like this:

Select 
    a.field1,
    b.field2,
    c.field3,
    c.field4,
    b.filed5,
    a.field6,
    COALESCE(SUM(d.paid_amt) OVER (PARTITION BY a.some_column), 0)  as amount_paid 
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 and d.field8 = a.field3
where 
    some conditions;

上面的输出将是这样的:

The output of the above would be something like this:

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

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

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

上面的查询返回如下:

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

但是预期结果如下:

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

请在这里提出任何建议.

Any suggestions are helpful here please.

推荐答案

您似乎只想要:

Select . . .
       COALESCE(SUM(d.paid_amt) OVER (PARTITION BY c.field04), 0)  as amount_paid 

这篇关于根据其他联接表列计算字段的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！