title: oracle sql join SUM result doubled on some rows
body: <p>I have the following two SQL statements which are returning double the sum on some rows and would appreciate some help sorting.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>My second trial, where i thought the joins were the issue, and which returns identical results is as follows:</p>
  
  <pre><code>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
  </code></pre>
  
  <p><strong>RESULT</strong></p>
  
  <pre><code>╔════════════╦════════════════╦══════════════╦══════════╦══════════════════╗
  ║  "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        ║
  ╚════════════╩════════════════╩══════════════╩══════════╩══════════════════╝
  </code></pre>
  
  <p>The error is in the first row, where the sum should be half of what is shown (46800). The other two results are correct.</p>
  
  <p>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.</p>
  
  <p>Would really appreciate help on this.</p>
  
  <p>thank you.</p>
  
  <p><strong>UPDATE</strong></p>
  
  <p>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:</p>
  
  <pre><code>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)   &lt;- added
          )
  WHERE A.ITMCOD LIKE 'PS%'
      AND C.QASTAT = 'RL'      &lt;- added
  GROUP BY A.ITMCOD
      ,I.ITMDSC
      ,A.CTLGRP
      ,C.FIFODT
      ,I.STPSHP
      ,C.STPDAT
  )
  ORDER BY ITMCOD
  </code></pre>