Sum On Multiple Left Joins

I am trying to sum the total value of a column for a specific ID after multiple left joins. The below code gives me what I am looking for but across multiple rows, I need the value

Solution 1:

Use aggregation with group by

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

Solution 2:

I would do it this way. Since Teradata is a MPP, there should not be much of a performance impact as well.

SELECT Unique_ID,C_Date,sum(C_Amount),D_Date,sum(D_Amount)
FROM
(
SELECT
      T1.ID ID,
      T2.Unique_ID Unique_ID,
      T3.C_Date C_Date,
      T3.C_Amount C_Amount,
      T4.D_Date D_Date,
      T4.D_Amount D_Amount
   FROM 
      TABLE_1 T1
         LEFTJOIN DATABASE1.TABLE_2 T2
            ON T1.ID = T2.UNIQUE_ID
            LEFTJOIN DATABASE1.TABLE_3 T3
               ON T2.Unique_ID = T3.Unique_ID
              AND T3.C_Date ='2019-04-11'LEFTJOIN DATABASE1.TABLE_4 T4
               ON T2.Unique_ID = T4.Unique_ID 
              AND T4.D_Date='2019-04-11'
) ABC
GROUPBY Unique_ID,C_Date,D_Date

Solution 3:

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.

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'groupby 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'groupby Unique_ID ) T4
            ON T1.ID = T4.Unique_ID 

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 ISNOTNULLOR  T4.Unique_ID ISNOTNULL