Avatar of chaitu chaitu
chaitu chaitu
Flag for India 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

Oracle Database

Avatar of undefined
Last Comment
awking00

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Guy Hengel [angelIII / a3]

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
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.