asked on

how to increment count when GROUP by is used

SNO column should be incremented depending upon the total records to be fetched.if i get 4 records then sno numbers should be 1,2,3,4.i dont want to put rownum also in the GROUP BY clause.how to increment the serial number?

SELECT JC.A,
             ROWNUM SNO, --serial number
             JC.B,
             SUM(CHR.AMOUNT),
             SUM(CHR.FINALAMOUNT),
             JC.C,
             JC.D,
             JC.E,
             JC.F,
             JC.G
        FROM CHARGES CHR
       WHERE JC.B = '12111'
         AND JC.STATUS = 'INVOICED'
       GROUP BY JC.A, JC.B, JC.C, JC.D, JC.E, JC.F, JC.G;

Open in new window

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

chaitu chaitu

ASKER

yours working .can i use this?

ROW_NUMBER() OVER (PARTITION BY
TRANSACTION_ID ORDER BY TRANSACTION_ID) SRLNO

awking00

>>can i use this?<<
Certainly, if you want the serial number to reset for each transaction_id. Can you post some sample data and your desired output?

sventhan

@chaituu -
This should work very well. Also, analytical functions has advantages over other SQLs.
ROW_NUMBER() OVER (PARTITION BY
TRANSACTION_ID ORDER BY TRANSACTION_ID) SRLNO

awking00

There are still a couple of issues, which is why I requested some sample data and the expected output. Do you want the serial number to start at one and increment for each transaction_id or simply to increment for each row? Also, you can't use group by with an analytical function so you would have to also use the analytic for the sums as well.