Using SUM and Distinct in innner join

• aznabeel

  SSC Veteran

  Points: 203

  More actions

  January 13, 2020 at 12:18 pm

  #3714870

  Table - CI_BSEG_READ

  

  Table -  CI_BSEG_SQ

  

  I want to sum the  MSR_QTY where BSEG ID is same in "CI_BSEG_READ " table and then match it with BSEG_ID in table "CI_BSEG_SQ"  and return  INIT_SQ where UOM is M3.

  My query is:

  select A. BSEG_ID ,  sum (A.MSR_QTY) , A.START_REG_READ_ID,B.INIT_SQ, B.UOM_CD

  from CI_BSEG_READ A

  INNER JOIN CI_BSEG_SQ B

  ON (A.BSEG_ID=B.BSEG_ID)

  WHERE B.UOM_CD ='M3'

  AND  A.BSEG_ID ='604392880319'

  GROUP BY A.BSEG_ID, B.BSEG_ID, A.MSR_QTY,A.START_REG_READ_ID,B.INIT_SQ, B.UOM_CD;

  Output:

  

  It is not adding the MSR_QTY

  Please tell me what is wring with my query

   

   

• Ant-Green

  SSC Guru

  Points: 112965

  More actions

  January 13, 2020 at 12:26 pm

  #3714871

  Your group by is to specific to perform the SUM you need.  Looking at the output you need to do away with START_REG_READ_ID and remove that and the MSR_QTY from the group by, something like the below

   

  select A. BSEG_ID ,  sum (A.MSR_QTY) , B.INIT_SQ, B.UOM_CD

  from CI_BSEG_READ A

  INNER JOIN CI_BSEG_SQ B

  ON (A.BSEG_ID=B.BSEG_ID)

  WHERE B.UOM_CD ='M3'

  AND  A.BSEG_ID ='604392880319'

  GROUP BY A.BSEG_ID, B.BSEG_ID, B.INIT_SQ, B.UOM_CD;