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;
Oracle Database
Last Comment
awking00
ASKER CERTIFIED SOLUTION
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.
>>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?
Certainly, if you want the serial number to reset for each transaction_id. Can you post some sample data and your desired output?
@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
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
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.
ROW_NUMBER() OVER (PARTITION BY
TRANSACTION_ID ORDER BY TRANSACTION_ID) SRLNO