Post reply

Using SUM and Distinct in innner join

  • January 13, 2020 at 12:18 pm

    #3714870

    Table - CI_BSEG_READ

    CI_BSEG_READ

    Table -  CI_BSEG_SQ

    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:

    Output query

    It is not adding the MSR_QTY

    Please tell me what is wring with my query

     

     

  • 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;

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply