[pl / SQL exercise 1] calculate the number of people who meet the conditions

Time:2020-8-23

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 departmentpercentage

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 fieldspercentagefield


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 carefulDon’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