# [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``````

## SQL exercise 20 – Modeling & Reporting

This blog is used to review and sort out the common topic modeling architecture, analysis oriented architecture and integration topic reports in data warehouse. I have uploaded these reports to GitHub. If you are interested, you can have a lookAddress:https://github.com/nino-laiqiu/TiTanI recorded a relatively complete development process in my hexo blog deployed on GitHub. You can […]