Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2009
    Posts
    51

    Unanswered: Summation and select distinct for summation.

    Please help on following

    Table:
    ITrans

    Primary key:
    uid, RefNo, type, spkey.

    Group by field :
    uid, Ino

    Sum field:
    IValue

    Requirement:
    1.) Group the recrods by uid,Ino sum(Ivalue) as totval.

    2.) select a distinct RefNo, Ino, totval from Itrans

    Note Refno cannot be repeated must be distinct.
    It does not matter there many Refno for the summation just first one
    will be ok.
    Please no cursors.

    Thanks alot

  2. #2
    Join Date
    Feb 2009
    Posts
    51
    did i miss something.
    I said please though

  3. #3
    Join Date
    Feb 2009
    Posts
    51
    Please find some data values as follows

    uid, refno, ino, type, spkey, ivalue, ovalue
    01, 101, xx, J,jon, -10, 100
    01, 102, xx, J,jon, 200, 100
    01, 103, zz, J,mart, -100, 500

    required
    uid, refno, ino, spkey, sum of ivalue, ovalue
    01, *101, xx, jon, 190, 100
    01, *103, zz, mart, -100, 500
    Last edited by dbfHelp; 07-07-09 at 23:07.

  4. #4
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    dbfHelp, This probably isn't a complete answer because I am not sure what you want (or what the actual data looks like). But it might get you started.
    Code:
    SELECT UID, INO, MIN(REFNO), SUM(IVALUE)
    FROM ITRANS
    GROUP BY UID, INO
    Another approach is to use the above query without the MIN(REFNO) as one derived table or common table expression and then use the row_count function to number the rows of the table and select where row_count number = 1 and join this to the derived/common table expression Group By query. Unfortunately, I don't have the row_count syntax in front of me right now and don't use it enough to write it from memory.

  5. #5
    Join Date
    Feb 2009
    Posts
    51
    Hi Stealth_DBA,

    absolutely greatful for your solution it just worked.
    Thank you very much

Posting Permissions

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