How to get a single row on a inner join not to double the sum

group byjoin;oracle-11gsum

I have two tables (DEP_DEZ & CAR_EXP):

ID  FK_ID_CHESTIONAR    TOTAL_E TOTAL_F TOTAL_STOC
261 644                 87      152     48

ID  MASA_TOTALA FK_ID_CHESTIONAR
405 20          644
406 45          644

if I make an:

SELECT CHT.ID,
       SUM(DEP_DEZ.TOTAL_E+DEP_DEZ.TOTAL_F+DEP_DEZ.TOTAL_STOC)
       FROM CHEST cht
            INNER JOIN DEP_DEZ
               ON DEP_DEZ.FK_ID_CHESTIONAR = CHT.ID
   GROUP BY CHT.ID

The result is 644 and 287 (87+152+48). And it is ok, but if I make:

SELECT CHT.ID,
       SUM(DEP_DEZ.TOTAL_E+DEP_DEZ.TOTAL_F+DEP_DEZ.TOTAL_STOC),
       SUM(CAR_EXP.MASA_TOTALA),
       FROM CHEST cht
            INNER JOIN DEP_DEZ
               ON DEP_DEZ.FK_ID_CHESTIONAR = CHT.ID
            INNER JOIN CAR_EXP
               ON CAR_EXP.FK_ID_CHESTIONAR = CHT.ID
   GROUP BY CHT.ID

the result is 644 and 574 (287*2 – not ok) and 65 (45+20 – ok)

How can I make not to double my "287" in sum?

Best Answer

You could use multiple SELECT Statements, like this:

SELECT CHT.ID,
   (SELECT SUM(TOTAL_E + TOTAL_F + TOTAL_STOC) 
   FROM DEP_DEZ WHERE FK_ID_CHESTIONAR = cht.ID),
   (SELECT SUM(MASA_TOTALA) FROM CAR_EXP WHERE FK_ID_CHESTIONAR = CHT.ID)
   FROM CHEST cht
GROUP BY CHT.ID