多个左联接的总和 [英] Sum on Multiple Left Joins

问题描述

我试图在多个左联接之后求和特定ID的列的总和.

I am trying to sum the total value of a column for a specific ID after multiple left joins.

下面的代码为我提供了我想要的东西，但是在多行中，我需要将T3.C_Amt和T4.E_Amt的值相加.

The below code gives me what I am looking for but across multiple rows, I need the value for T3.C_Amt and T4.E_Amt to be totaled.

SELECT
      T1.ID,
      T2.Unique_ID,
      T3.C_Date,
      T3.C_Amount,
      T4.D_Date,
      T4.D_Amount
   FROM 
      TABLE_1 T1
         LEFT JOIN DATABASE1.TABLE_2 T2
            ON T1.ID = T2.UNIQUE_ID
            LEFT JOIN DATABASE1.TABLE_3 T3
               ON T2.Unique_ID = T3.Unique_ID
              AND T3.C_Date = '2019-04-11'
            LEFT JOIN DATABASE1.TABLE_4 T4
               ON T2.Unique_ID = T4.Unique_ID 
              AND T4.D_Date= '2019-04-11'


--this needs to be summed to have the total amount

我希望它返回唯一ID的一行，其中包含特定日期的总C_Amount和总D_Amount

I want it to return one row for the Unique ID with total C_Amount and total D_Amount for the specific date

推荐答案

我要添加一个一对多"的担忧，这会导致总计错误.如果表3对于给定的T2.UniqueID有10条记录，而对于T4表又有5条记录，该怎么办.您刚刚使您的总数完全超出了范围.

I would add a concern of 1-to-many causing a false total. What if table 3 has 10 records for a given T2.UniqueID and another 5 for the T4 table. You have just compounded your total completely out of range.

因此，我将从按日期过滤的唯一ID分组的子表中预先聚合.另外，由于具有关联属性，您可以删除T2表.

As such, I would pre-aggregate from the child tables grouped by the unique ID filtered on the date. Also, you can remove the T2 table due to associative properties.

T1.ID = T2.Unique_ID = T3.Unique_ID = T4.Unique_ID
to T1.ID = T3.Unique_ID = T4.Unique_ID


SELECT
      T1.ID,
      T3.C_Date,
      T3.C_Amount,
      T4.D_Date,
      T4.D_Amount
   FROM 
      TABLE_1 T1
         LEFT JOIN 
         ( Select Unique_ID, sum( C_Amount ) as T3Sum
              from DATABASE1.TABLE_3
              where T3.C_Date = '2019-04-11'
              group by Unique_ID ) T3
            ON T1.ID = T3.Unique_ID
         LEFT JOIN 
         ( select Unique_ID, sum( D_Amount ) T4Sum                 
              from DATABASE1.TABLE_4 
              where D_Date= '2019-04-11'
              group by Unique_ID ) T4
            ON T1.ID = T4.Unique_ID 

现在，您对表名的歧义可能有助于更真实地描述.您的汇总金额基于单个日期，但是T1中有多少条记录适用?如果T1中有5k行，而表T3和T4之间总共只有450个条目，那么结果集仍将为您提供所有行.话虽这么说，您可能不希望在次要总和子查询中没有任何此类详细信息的记录膨胀.我会在末尾添加一个WHERE子句

Now, your ambiguity on table names might help being more real-life descriptive. Your summary amounts are based on a single date, but how many records in T1 that are applicable? If you have 5k rows in T1 and only 450 entries total between tables T3 and T4, your result set would still give you all the rows. That being said, you probably dont want the bloat of records that don't have any such details in the secondary sum subqueries. I would add a WHERE clause at the end

   WHERE
          T3.Unique_ID IS NOT NULL
      OR  T4.Unique_ID IS NOT NULL

这篇关于多个左联接的总和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！