CODEWITHSUNDEEP
sqloracle-database
Neo
Neo

Reputation: 4760

RANKing in group by in oracle

I have a query 

Select age,qualification,sum(income) as total_income  from employee
group by age,qualification;

I want to find rank on based total_income for the group of age and qualification.

For example

19|Grad|5000|rank:1
19|Grad|4000|rank:2
19|Grad|3000|rank:3
26|Grad|6000|rank:1
26|Grad|5000|rank:2
26|PosG|8000|rank:1
26|PosG|6000|rank:2

Can I do it in Oracle? I tried with partition by but not able to figure it out.

Upvotes: 4

Views: 19442

Answers (3)

seema
seema

Reputation: 11

Select 
          Age,
          Qualification,
          sum(income) as totalIncome,
          dense_rank () over (order by 
                             sum(income) desc) as DRnk    from exam_RG 
    group by age,Qualification

Age Qualification   totalIncome DRnk
26  Grad            21000        1
19  Grad            12000        2

Upvotes: 0

MT0
MT0

Reputation: 167822

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE Employees ( Age, Qualification, Income ) AS
          SELECT 19, 'Grad', 5000 FROM DUAL
UNION ALL SELECT 19, 'Grad', 4000 FROM DUAL
UNION ALL SELECT 19, 'Grad', 3000 FROM DUAL
UNION ALL SELECT 26, 'Grad', 6000 FROM DUAL
UNION ALL SELECT 26, 'Grad', 5000 FROM DUAL
UNION ALL SELECT 26, 'PosG', 8000 FROM DUAL
UNION ALL SELECT 26, 'PosG', 6000 FROM DUAL;

Query 1:

SELECT Age,
       Qualification,
       Income,
       RANK() OVER ( PARTITION BY Age, Qualification ORDER BY Income DESC ) AS "Rank"
FROM   Employees

Results:

| AGE | QUALIFICATION | INCOME | Rank |
|-----|---------------|--------|------|
|  19 |          Grad |   5000 |    1 |
|  19 |          Grad |   4000 |    2 |
|  19 |          Grad |   3000 |    3 |
|  26 |          Grad |   6000 |    1 |
|  26 |          Grad |   5000 |    2 |
|  26 |          PosG |   8000 |    1 |
|  26 |          PosG |   6000 |    2 |

Query 2:

WITH total_incomes AS (
  SELECT Age,
         Qualification,
         SUM( Income ) AS total_income
  FROM   Employees
  GROUP BY
         Age,
         Qualification
)
SELECT Age,
       Qualification,
       total_income,
       RANK() OVER ( ORDER BY total_income DESC ) AS "Rank"
FROM   total_incomes

Results:

| AGE | QUALIFICATION | TOTAL_INCOME | Rank |
|-----|---------------|--------------|------|
|  26 |          PosG |        14000 |    1 |
|  19 |          Grad |        12000 |    2 |
|  26 |          Grad |        11000 |    3 |

Upvotes: 7

Madhivanan
Madhivanan

Reputation: 13700

select  age,qualification,total_income
row_number() over (partition by age,qualification order by income desc) as rank from
(
Select age,qualification,sum(income) as total_income  from employee
group by age,qualification
) T1

Upvotes: 0

Related Questions