Rating in the group in the oracle
I have a request
Select age,qualification,sum(income) as total_income from employee
group by age,qualification;
I want to find a rank based on total_income for a group of age and qualifications.
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 this in Oracle? I tried with the section but couldn't figure it out.
+3
source to share
2 answers
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;
Request 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 |
Request 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 |
+3
source to share