Practice > SQL > Aggregation > Top Earners

Problem

We define an employee's total earnings to be their monthly worked, and the maximum total earnings to be the maximum total earnings for any employee in the Employee table. Write a query to find the maximum total earnings for all employees as well as the total number of employees who have maximum total earnings. Then print these values as space-separated integers.
문제링크

Answer

1. WHERE에 서브쿼리를 이용한 풀이

SELECT months*salary as earnings
      , COUNT(*)
FROM employee
WHERE months*salary = (SELECT MAX(months*salary) FROM employee) -- WHERE절에서는 AS사용 불가
GROUP BY earnings -- GROUP BY 부터는 AS 사용가능

2. HAVING절 서브쿼리를 이용한 풀이

SELECT months*salary as earnings
	 , COUNT(*)
FROM employee
GROUP BY earnings -- GROUP BY 부터는 AS 사용가능
HAVING earnings = (SELECT MAX(months*salary) FROM employee) -- 여기에서 MAX()에는 earnings를 쓰면 안되네

3. 집계함수를 이용한 풀이

SELECT months * salary as earnings, count(*)
FROM employee
GROUP BY earnings -- 여기서부터 AS 사용 가능함
ORDER by earnings desc
LIMIT 1