SQL - JOIN AN AGGREGATE TABLE WITH A NONAGGREGATE TABLE

Question

I have written the following code that selects the aggregate remaining interest payments from each loan:

SELECT
a.PARENTREF,
a.INTEREST - Isnull(b.INTEREST,0) AS INTEREST

FROM
(SELECT 
PARENTREF,
SUM(TOTAL) AS INTEREST
   FROM LG_011_BNCREPAYTR 
   WHERE TRANSTYPE = 0 
   GROUP BY PARENTREF) a LEFT OUTER JOIN
(SELECT
PARENTREF,
SUM(TOTAL) AS INTEREST
   FROM LG_011_BNCREPAYTR 
   WHERE TRANSTYPE = 1 
   GROUP BY PARENTREF) b
ON a.PARENTREF = b.PARENTREF

It brings a table like the following:

PARENTREF       INTEREST
        1         500.93
        2         510.00
        3        1200.90
        4         500.93

Now, there is another table in the database that holds other info regarding the loans like the loan description. The LOGICALREF of that table is the PARENTREF in this table. That table is as follows:

 LOGICALREF         DESCRIPTION        CURRENCYREF           DUEDATE
          1            CAR LOAN                  0        2015-10-01
          2       CONSUMER LOAN                  2        2015-10-01
          3            CAR LOAN                  4        2015-10-01
          4            CAR LOAN                  3        2015-10-01

I am trying to combine my table with the last table that I mentioned, but for some reason I get the DESCRIPTION, CURRENCYREF and the DUEDATE as NULL. I think there is something wrong due to the GROUP BY that I am using because if I don't aggregate, these functions work. The table that I want is the following:

PARENTREF       INTEREST          DESCRIPTION     CURRENCYREF       DUEDATE 
        1         500.93             CAR LOAN               0    2015-10-01
        2         510.00        CONSUMER LOAN               2    2015-10-01
        3        1200.90             CAR LOAN               4    2015-10-01
        4         500.93             CAR LOAN               3    2015-10-01

THANKS A LOT.

score 0 · Accepted Answer · answered Dec 22 '14 at 11:17

Try this:

SELECT a.PARENTREF, a.INTEREST - ISNULL(b.INTEREST, 0) AS INTEREST, 
       T.DESCRIPTION, T.CURRENCYREF, T.DUEDATE 
FROM YourTable T 
INNER JOIN (SELECT PARENTREF, SUM(TOTAL) AS INTEREST
            FROM LG_011_BNCREPAYTR 
            WHERE TRANSTYPE = 0 
            GROUP BY PARENTREF) A ON T.LOGICALREF = A.PARENTREF
LEFT OUTER JOIN (SELECT PARENTREF, SUM(TOTAL) AS INTEREST
                 FROM LG_011_BNCREPAYTR 
                 WHERE TRANSTYPE = 1 
                 GROUP BY PARENTREF
                ) b ON a.PARENTREF = b.PARENTREF;

OR

You can also find solution using below query

SELECT A.PARENTREF, 
      (SUM(CASE WHEN A.TRANSTYPE = 0 THEN A.TOTAL ELSE 0 END) - SUM(CASE WHEN A.TRANSTYPE = 1 THEN A.TOTAL ELSE 0 END)) AS INTEREST, 
       T.DESCRIPTION, T.CURRENCYREF, T.DUEDATE 
FROM YourTable T 
INNER JOIN LG_011_BNCREPAYTR A ON T.LOGICALREF = A.PARENTREF 
GROUP BY A.PARENTREF;

SQL - JOIN AN AGGREGATE TABLE WITH A NONAGGREGATE TABLE

1 Answers1