Question:

The number of employees whose salary is higher than the average wage of each department accounts for the total number of employees in the whole department**percentage**

Analysis: all the information here comes from the EMP table, and there is no need to do table join.

Secondly, to add a new column of the average wage of each department, here we use the analysis function avg() over (Parton by…) and then we need to calculate the percentage. The numerator is the number of people above the average wage, and the denominator is the number of people in the whole department

keyword:

1）**Calculate the cumulative sum of the number of people who meet certain conditions**

2) Generate fields**percentage**field

One of the difficulties: how to write the number of people who are higher than the average wage? And keep count() of all the people in the whole table

Solution: case when satisfies count + 1, otherwise 0; then use full table as subquery

**be careful**Don’t use count because this only counts,**We don’t sum everything that meets the conditions**

```
SELECT A.DEPTNO,
TO_CHAR(ROUND(SUM(CASE
WHEN A.SAL > A.AVG THEN
1
ELSE
0
END) / COUNT(*) * 100,
2),
'99d99') | '%' as percentage -- because to highlight that count (*) is the total number of people, subquery requires E*
FROM (SELECT E.*, AVG(E.SAL) OVER(PARTITION BY E.DEPTNO) AVG FROM EMP E) A
GROUP BY A.DEPTNO
```