oracle sql join SUM result doubled on some rows

Question

I have the following two SQL statements which are returning double the sum on some rows and would appreciate some help sorting.

SELECT *
FROM (
SELECT A.ITMCOD
    ,I.ITMDSC
    ,A.CTLGRP
    ,SUM(A.CASQTY) thesum
    ,(C.FIFODT + I.STPSHP) STOP_SHIP_DATE
FROM INVENT_VIEW A
INNER JOIN ITMMST I ON A.ITMCOD = I.ITMCOD
INNER JOIN CTLGRP C ON A.CTLGRP = C.CTLGRP
WHERE A.ITMCOD LIKE 'PS%687%'
GROUP BY A.ITMCOD
    ,I.ITMDSC
    ,A.CTLGRP
    ,C.FIFODT
    ,I.STPSHP
)
ORDER BY ITMCOD

My second trial, where i thought the joins were the issue, and which returns identical results is as follows:

SELECT INVENT_VIEW.ITMCOD
  ,ITMMST.ITMDSC
  ,CTLGRP.CTLGRP
  ,SUM(INVENT_VIEW.CASQTY) thesum
  ,(CTLGRP.FIFODT + ITMMST.STPSHP) STOP_SHIP_DATE
FROM INVENT_VIEW
  ,CTLGRP
  ,ITMMST
WHERE ITMMST.ITMCOD = INVENT_VIEW.ITMCOD
  AND (CTLGRP.CTLGRP = INVENT_VIEW.CTLGRP)
  AND (INVENT_VIEW.ITMCOD LIKE 'PS%687%')
GROUP BY INVENT_VIEW.ITMCOD
  ,ITMMST.ITMDSC
  ,CTLGRP.CTLGRP
  ,CTLGRP.FIFODT
  ,ITMMST.STPSHP

RESULT

╔════════════╦════════════════╦══════════════╦══════════╦══════════════════╗
║  "ITMCOD"  ║    "ITMDSC"    ║   "CTLGRP"   ║ "THESUM" ║ "STOP_SHIP_DATE" ║
╠════════════╬════════════════╬══════════════╬══════════╬══════════════════╣
║ "PS00687A" ║ "gROOVY BRUSH" ║ "4066ZDPOO4" ║    93600 ║ 26-DEC-16        ║
║ "PS00687A" ║ "gROOVY BRUSH" ║ "5385POSOO4" ║     3600 ║ 08-MAY-18        ║
║ "PS00687A" ║ "gROOVY BRUSH" ║ "5299POS001" ║     9922 ║ 08-MAY-18        ║
╚════════════╩════════════════╩══════════════╩══════════╩══════════════════╝

The error is in the first row, where the sum should be half of what is shown (46800). The other two results are correct.

I have tried putting DISTINCT right after sum, but this just returns the first value for the first row which is 3600. i have also placed distinct right after the SELECT statement but this makes no difference.

Would really appreciate help on this.

thank you.

UPDATE

Thanks to mef who was right about duplicates in the joined tables, i was able to get my desired results! I also edited the SQL to this:

SELECT *
FROM (
    SELECT A.ITMCOD
    ,I.ITMDSC
    ,A.CTLGRP
    ,SUM(A.CASQTY) QTY
    ,CASE 
        WHEN C.STPDAT IS NOT NULL
            THEN C.STPDAT
        ELSE (C.FIFODT + I.STPSHP)
        END AS STOP_SHIP_DATE
FROM INVENT_VIEW A
INNER JOIN ITMMST I ON A.ITMCOD = I.ITMCOD
INNER JOIN CTLGRP C ON (
        (A.CTLGRP = C.CTLGRP)
        AND (A.ITMCOD = C.ITMCOD)   <- added
        )
WHERE A.ITMCOD LIKE 'PS%'
    AND C.QASTAT = 'RL'      <- added
GROUP BY A.ITMCOD
    ,I.ITMDSC
    ,A.CTLGRP
    ,C.FIFODT
    ,I.STPSHP
    ,C.STPDAT
)
ORDER BY ITMCOD

Answer 1

There may be a duplicate for your first record inside INVENT_VIEW or CTLGRP.

Check with these two queries, each of them should return one in case the data is correct:

-- check whether there is a duplicate inside INVENT_VIEW
select count(1) from INVENT_VIEW where CTLGRP = '4066ZDPOO4' and ITMCOD = 'PS00687A'

-- check whether there is a duplicate inside CTLGRP
select count(1) from CTLGRP where CTLGRP = '4066ZDPOO4'

In case one of these queries returns a value above than one, then there is a duplicate in your source data. (You may want to double-check the view's query).

Otherwise, if both the queries return one, there's a problem with your join. In such case please share the structure of your tables.