GROUP BY

SupplierID 별로 Price의 평균을 보고 싶을 경우에는?

SELECT SupplierID
     , AVG(Price)
FROM Products
GROUP BY SupplierID

GROUP BY에 사용된 컬럼은 SELECT에 꼭 들어가야하고 그룹 별 보고싶은 집계함수도 SELECT에 들어가야한다.

GROUP BY 여러 개의 칼럼 : 여러 가지 기준으로 그룹을 만들고 싶을 때

SELECT SupplierID
     , Categoryid
     , AVG(Price)
FROM Products
GROUP BY SupplierID, Categoryid

HAVING

GROUP BY 결과물들 중에서 AVG(Price) >= 100 이상인 것을 찾고 싶다면?

WHERE AVG(Price) >= 100 으로 하면 안된다!

WHERE 문은 GROUP BY를 하기 전에 실행되기 때문이다.

GROUP BY 에 필터링을 걸어주고 싶다면 HAVING을 사용해야 한다.

SELECT SupplierID,  AVG(Price)
FROM Products
GROUP BY SupplierID
HAVING AVG(Price) >= 100;

AS

AVG(Price)를 AS를 사용하여 다른 명칭으로 지정할 수 있다.

SELECT SupplierID
     , AVG(Price) AS avg_price
FROM Products
GROUP BY SupplierID
HAVING avg_price >= 100;

HackerRank: Top Earners

We define an employee's total earnings to be their monthly salary X month 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 2 space-separated integers.

Input Format

The Employee table containing employee data for a company is described as follows:

where employee_id is an employee's ID number, name is their name, months is the total number of months they've been working for the company, and salary is the their monthly salary.

Sample Input

Sample Output

69952 1

Explanation

The table and earnings data is depicted in the following diagram:

The maximum earnings value is 69952. The only employee with earnings = 69952 is Kimberly, so we print the maximum earnings value (69952) and a count of the number of employees who have earned $69952 (which is 1) as two space-separated values.

1. salary X month = earnings
2. 각 earning 별로 몇 명이 있는지 GROUP BY
3. earning 중에 가장 큰 값을 가져옴 ORDER BY , LIMIT

SELECT salary * months AS earnings
      , COUNT(*)
FROM Employee
GROUP BY earnings
ORDER BY earnings DESC
LIMIT 1;

728x90