Top Earners

---

Link

We define an employee's total earnings to be their monthly $salary \times months$ worked, and the maximum total earnings to be the maximum total earnings for any employee in the Employee table.

1) Write a query to find the maximum total earnings for all employees

2) as well as the total number of employees who have maximum total earnings.

3) Then print these values as 2 space-separated integers.

Sample Output

69952 1

풀이

---

• Employee 테이블 : employee_id, name, months, salary
• earnings = $salary \times months$

1) 가장 높은 소득 금액 찾기
2) 가장 높은 소득을 받고 있는 직원의 인원 수
3) 1,2 번 결과값 사이에 공백 2개를 넣고 출력

---

방법1. WHERE절 서브쿼리 활용

1. 소득($salary \times months$) 이 가장 높은 사람들의 목록 뽑기

SELECT months * salary AS earnings
	 , name
FROM employee
WHERE months * salary = (SELECT MAX(months*salary) FROM employee)

108064 Anthony 
108064 Nancy 
108064 Patricia 
108064 Angela 
108064 Gregory 
108064 Lillian 
108064 Evelyn 

2. Sample Output (최고소득금액 인원수) 형태로 출력해주기 위해, GROUP BY 를 활용해서 몇 명인지 카운트

SELECT months * salary AS earnings
	 , COUNT(*)
FROM employee
WHERE months * salary = (SELECT MAX(months*salary) FROM employee)
GROUP BY earnings

108064 7 

📌 SELECT절에 써준 alias

• WHERE절 사용 불가
• GROUP BY 사용 가능
• HAVING절 사용 가능

---

방법2. HAVING절 서브쿼리 활용

1. 소득별(earnings) 직원 수를 출력하기 위해 GROUP BY 사용

SELECT months * salary AS earnings
     , COUNT(*)
FROM employee
GROUP BY earnings

1860 1 
2086 1 
2113 1 
2438 1 
2492 1 
2717 1 

2. GROUP BY 결과물을 다시 한 번 필터링 해주기 위해서 HAVING절 사용

SELECT months * salary AS earnings
     , COUNT(*)
FROM employee
GROUP BY earnings
HAVING earnings = (SELECT MAX(months * salary) FROM employee)

My Answer

---

SELECT months * salary AS earnings
	 , COUNT(*)
FROM employee
WHERE months * salary = (SELECT MAX(months*salary) FROM employee)
GROUP BY earnings