Rows Getting duplicated while doing summation

I have 3 tables. The join between first two tables in CONTR_ID and between second and third table is ENC_RCPT_ID.

CONTR

+----+----------+------+-----------+
| ID | CONTR_ID | CURR | TOT_CONTR |
+----+----------+------+-----------+
| 1  | 1        | USD  | 100       |
+----+----------+------+-----------+
| 2  | 2        | USD  | 80        |
+----+----------+------+-----------+

INS_RCPT

+----+----------+-------------+-----------+----------+
| ID | CONTR_ID | ENC_RCPT_ID | RCPT_CURR | RCPT_AMT |
+----+----------+-------------+-----------+----------+
| 1  | 1        | 1           | USD       | 20       |
+----+----------+-------------+-----------+----------+
| 2  | 1        | 2           | USD       | 30       |
+----+----------+-------------+-----------+----------+
| 3  | 1        | 3           | USD       | 50       |
+----+----------+-------------+-----------+----------+
| 4  | 2        | 4           | USD       | 60       |
+----+----------+-------------+-----------+----------+
| 5  | 2        | 5           | USD       | 20       |
+----+----------+-------------+-----------+----------+

ENC_RCPT

+----+-------------+--------------+
| ID | ENC_RCPT_ID | ENC_RCPT_AMT |
+----+-------------+--------------+
| 1  | 1           | 5            |
+----+-------------+--------------+
| 2  | 1           | 15           |
+----+-------------+--------------+
| 3  | 2           | 5            |
+----+-------------+--------------+
| 4  | 2           | 10           |
+----+-------------+--------------+
| 5  | 2           | 15           |
+----+-------------+--------------+

I want to show output as below:

+----------+------+-----------+----------+--------------+
| CONTR_ID | CURR | TOT_CONTR | RCPT_AMT | ENC_RCPT_AMT |
+----------+------+-----------+----------+--------------+
| 1        | USD  | 100       | 100      | 50           |
+----------+------+-----------+----------+--------------+
| 2        | USD  | 80        | 80       | 0            |
+----------+------+-----------+----------+--------------+

I have written below select query but it does not give correct result.

SELECT C.contr_id, C.curr, C.tot_contr, IR.rcpt_amt, ER.enc_rcpt_amt 
FROM   @CONTR AS C 
       LEFT OUTER JOIN (SELECT contr_id, 
                               enc_rcpt_id, 
                               Sum(rcpt_amt) AS RCPT_AMT 
                        FROM   @INS_RCPT 
                        GROUP  BY contr_id, 
                                  enc_rcpt_id) AS IR 
                    ON IR.contr_id = C.contr_id 
       LEFT OUTER JOIN (SELECT ER1.enc_rcpt_id, 
                               Sum(ER1.enc_rcpt_amt) AS ENC_RCPT_AMT 
                        FROM   @ENC_RCPT AS ER1 
                               INNER JOIN @INS_RCPT AS IR1 
                                       ON IR1.enc_rcpt_id = ER1.enc_rcpt_id 
                        GROUP  BY ER1.enc_rcpt_id) AS ER 
                    ON ER.enc_rcpt_id = IR.enc_rcpt_id

Answer

You have to force everything to be at the same grain, so you have to roll it all the way up.

SELECT C.contr_id, C.curr, C.tot_contr, r.rcpt_amt, r.enc_rcpt_amt
from
contr c 
left outer join (select
contr_id,
sum(rcpt_amt) as rcpt_amt,
sum(enc_rcpt_amt) as enc_rcpt_amt
from

(SELECT contr_id, 
                               enc_rcpt_id, 
                               Sum(rcpt_amt) AS RCPT_AMT 
                        FROM   INS_RCPT 
                        GROUP  BY contr_id, 
                                  enc_rcpt_id) AS IR 
       LEFT OUTER JOIN (SELECT ER1.enc_rcpt_id, 
                               Sum(ER1.enc_rcpt_amt) AS ENC_RCPT_AMT 
                        FROM   ENC_RCPT AS ER1 
                               INNER JOIN INS_RCPT AS IR1 
                                       ON IR1.enc_rcpt_id = ER1.enc_rcpt_id 
                        GROUP  BY ER1.enc_rcpt_id) AS ER 
                    ON ER.enc_rcpt_id = IR.enc_rcpt_id 
  group by
  contr_id) r 
  on c.contr_id = r.contr_id

​x
 
SELECT C.contr_id, C.curr, C.tot_contr, r.rcpt_amt, r.enc_rcpt_amtfromcontr c left outer join (selectcontr_id,sum(rcpt_amt) as rcpt_amt,sum(enc_rcpt_amt) as enc_rcpt_amtfrom​(SELECT contr_id,                                enc_rcpt_id,                                Sum(rcpt_amt) AS RCPT_AMT                         FROM   INS_RCPT                         GROUP  BY contr_id,                                   enc_rcpt_id) AS IR        LEFT OUTER JOIN (SELECT ER1.enc_rcpt_id,                                Sum(ER1.enc_rcpt_amt) AS ENC_RCPT_AMT                         FROM   ENC_RCPT AS ER1                                INNER JOIN INS_RCPT AS IR1                                        ON IR1.enc_rcpt_id = ER1.enc_rcpt_id                         GROUP  BY ER1.enc_rcpt_id) AS ER                     ON ER.enc_rcpt_id = IR.enc_rcpt_id   group by  contr_id) r   on c.contr_id = r.contr_id​

Advertisement

Answer