Home » SQL & PL/SQL » SQL & PL/SQL » how to increment count when GROUP by is used (oracle 10g)
how to increment count when GROUP by is used [message #443761] Wed, 17 February 2010 04:37 Go to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member

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;



Re: how to increment count when GROUP by is used [message #443763 is a reply to message #443761] Wed, 17 February 2010 04:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ROW_NUMBER

Regards
Michel

[Updated on: Wed, 17 February 2010 04:46]

Report message to a moderator

Re: how to increment count when GROUP by is used [message #443764 is a reply to message #443761] Wed, 17 February 2010 04:46 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
As you are grouping the records, Oracle will return one row for one group. So that you have to use aggrigate function for non-grouping column.
Try for analityc function.

regards,
Delna
Re: how to increment count when GROUP by is used [message #443770 is a reply to message #443761] Wed, 17 February 2010 04:55 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
Is this ok?

ROW_NUMBER() OVER (PARTITION BY
A ORDER BY A) SRLNO
Re: how to increment count when GROUP by is used [message #443771 is a reply to message #443770] Wed, 17 February 2010 04:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What happened when you tried?
Only YOU know how you want to number your rows.

Regards
Michel
Re: how to increment count when GROUP by is used [message #443773 is a reply to message #443761] Wed, 17 February 2010 05:03 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
yea result is coming as expected.
Re: how to increment count when GROUP by is used [message #443775 is a reply to message #443771] Wed, 17 February 2010 05:05 Go to previous messageGo to next message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select deptno, job, count(*) nb, 
  2         row_number() over (order by deptno, job) overall_num,
  3         row_number() over (partition by deptno order by job) indept_num
  4  from emp
  5  group by deptno, job
  6  order by deptno, job
  7  /
    DEPTNO JOB               NB OVERALL_NUM INDEPT_NUM
---------- --------- ---------- ----------- ----------
        10 PRESIDENT          1           1          1
        20 ANALYST            2           2          1
        20 CLERK              2           3          2
        20 MANAGER            1           4          3
        30 CLERK              1           5          1
        30 MANAGER            1           6          2
        30 SALESMAN           4           7          3

7 rows selected.

Regards
Michel
Re: how to increment count when GROUP by is used [message #443781 is a reply to message #443775] Wed, 17 February 2010 05:27 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
Michel Cadot wrote on Wed, 17 February 2010 16:35
SQL> select deptno, job, count(*) nb, 
  2         row_number() over (order by deptno, job) overall_num,
  3         row_number() over (partition by deptno order by job) indept_num
  4  from emp
  5  group by deptno, job
  6  order by deptno, job
  7  /
    DEPTNO JOB               NB OVERALL_NUM INDEPT_NUM
---------- --------- ---------- ----------- ----------
        10 PRESIDENT          1           1          1
        20 ANALYST            2           2          1
        20 CLERK              2           3          2
        20 MANAGER            1           4          3
        30 CLERK              1           5          1
        30 MANAGER            1           6          2
        30 SALESMAN           4           7          3

7 rows selected.

Regards
Michel


i have one clarification.what i understood about partition by is it will group the deptno and assign the ranks.
Re: how to increment count when GROUP by is used [message #443783 is a reply to message #443781] Wed, 17 February 2010 05:29 Go to previous message
Michel Cadot
Messages: 64140
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes you can say that.

Regards
Michel
Previous Topic: RETURN MONTH
Next Topic: update using subquery
Goto Forum:
  


Current Time: Fri Dec 09 03:36:08 CST 2016

Total time taken to generate the page: 0.27406 seconds