Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2013
    Posts
    2

    Unanswered: Sum the distinct docid's

    select distinct
    gt.docid,
    gt.act_id,
    Sum(gt.gm_amt) amt
    from gtemp gt
    WHERE gt.gm_amt != '0' AND
    docid IN ('12343','436220','asde32','641154','frd456')
    Group BY
    gt.docid,
    gt.act_id

    These are the actual results
    docid act_id amt
    12343 2115 -619.31
    12343 2115 -2.77
    12343 2115 2.77
    12343 2115 619.31
    436220 2115 -28
    436220 2115 28
    asde32 2115 -2876.16
    641154 2115 -24315.62
    frd456 2115 315.62

    But after I sum and and say amt not equal to 0 I have to get only last 3 records which are asde32,641154,frd456 but my results are something like this
    docid act_id amt
    12343 2115 0
    12343 2115 0
    12343 2115 0
    12343 2115 0
    436220 2115 0
    436220 2115 0
    asde32 2115 0
    641154 2115 0
    frd456 2115 0

  2. #2
    Join Date
    Dec 2012
    Location
    Logan, Utah
    Posts
    163
    If I am reading this correctly, you should

    1. Adjust your Where clause to:

    Code:
    WHERE docid IN ('12343','436220','asde32','641154','frd456')
    Just lose the gt.gm_amt != '0' part

    2. Add a

    Code:
    Having SUM(gt.gm_amt) <> 0
    after the group clause

    3. Make sure that you are not comparing numerics against a character string in the rest of your code.

    Code:
    gt.gm_amt != '0'
    HTH

  3. #3
    Join Date
    Feb 2013
    Posts
    2

    Thank you

    ..that query worked and results are accurate

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •