SQL使用内部联接和分组依据获取所有行的总和 [英] SQL Getting sum of all rows with Inner join and group by

问题描述

我正在尝试获取相同 DueDate 的行总和.我正在加入2个表格来填充其他字段.但是，我在汇总 Amount 列上的值时遇到困难，因此对于特定的 DueDate ，我只能以一行结尾除了 Amount 和 DueDate .其他所有列均具有相同的值.

I am trying to get the sum of rows of the same DueDate. I'm joing 2 tables to populate other fields. However, I'm having difficulty aggregating the values on Amount column so that I can end up with one row for a specific DueDate The other columns all have the same value except the Amount and DueDate.

以下是我的查询

SELECT
T2.Company,
T2.Code
T2.DueDate,
SUM(T2.Amount)

FROM TBL2 T2 
LEFT JOIN
    TBL1 T1 
    ON T2.Company = T1.Company 
    AND T2.Code IN 
    (
       0, 1, 2, 3, 4, 5, 6, 7, 8
    )
GROUP BY
T1.Company,T2.Code,T2.DueDate,T2.Amount

运行查询时，我得到:

仅汇总DueDate为'20200604'的行.(5000 + 5000 = 10000).

Only the rows with DueDate of '20200604' is aggregated. (5000 + 5000 = 10000).

当值不同并且有表联接时，如何汇总和分组?

How do I aggregate and group by when values are different and when there's a table join?

我将不胜感激.

谢谢.

推荐答案

您是否正在寻找正确的汇总?

Are you just looking for the right aggregation?

SELECT T2.Company, T2.Code T2.DueDate, SUM(T2.Amount)
FROM TBL2 T2 LEFT JOIN
     TBL1 T1 
     ON T2.Company = T1.Company AND
        T2.Code IN  0, 1, 2, 3, 4, 5, 6, 7, 8)
GROUP BY T1.Company, T2.Code, T2.DueDate;

即 AMOUNT 不属于 GROUP BY .

这篇关于SQL使用内部联接和分组依据获取所有行的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！