[SQL] 집계 함수(COUNT, SUM, AVG, MIN/MAX) 및 GROUP BY, ORDER BY

Posted on | In
[데이터 분석을 위한 중급 SQL] 집계 함수 및 GROUP BY, ORDER BY에 대한 간략 정리 및 해커랭크 문제 풀이

간략 정리

Id Name Visits
1 A 1
2 A 2
3 B 3
4 C 5
5 NULL NULL

COUNT

1
2
3
4
5
6
7
8
9
10
11
SELECT COUNT(*)
FROM sample;
> 5

SELECT COUNT(Name)
FROM sample;
> 4

SELECT COUNT(DISTINCT Name)
FROM sample;
> 3

SUM

1
2
SELECT SUM(Visits)
FROM sample;

AVG

1
2
3
4
5
6
SELECT AVG(Visits)
FROM sample;
> (1+2+3+5) / 4 = 2.75

SELECT SUM(Visits)/COUNT(*) FROM sample;
> (1+2+3+5) / 5 = 2.2

MAX/MIN

1
2
3
4
5
SELECT MAX(Visits)
FROM sample;

SELECT MIN(Visits)
FROM sample;

GROUP BY

1
2
3
SELECT CategoryID, SUM(Price)
FROM Products
GROUP BY CategoryID;

(그룹화의 기준이 되는 컬럼은 SELECT 구문에 반드시 적어주기)

HAVING

1
2
3
4
SELECT CategoryID, COUNT(*)
FROM Products
GROUP BY CategoryID
HAVING COUNT(*) <= 10

ORDER BY

  • 오름차순(Default)
    • ASC ascending
  • 내림차순
    • DESC descending
1
2
3
4
5
SELECT *
FROM Products
WHERE Price >= 20
ORDER BY price DESC;
(Price가 20이상인 값들 중 비싼 순으로 정렬)

LIMIT

1
2
3
4
SELECT *
FROM Products
ORDER BY price DESC
LIMIT 1;

(가장 비싼 물건 1개 출력)

해커랭크 문제 풀이

Average Population

  • Query the average population for all cities in CITY, rounded down to the nearest integer.
  • Input Format
  • The CITY table is described as follows:

image

풀이

1
2
SELECT ROUND(AVG(population))
FROM city

Revising Aggregations - The Sum Function

  • Query the total population of all cities in CITY where District is California.

풀이

1
2
3
SELECT SUM(population)
FROM city
WHERE district = 'California'

Revising Aggregations - Averages

  • Query the average population of all cities in CITY where District is California.

풀이

1
2
3
SELECT avg(population)
FROM city
WHERE district = 'California'

Revising Aggregations - The Count Function

  • Query a count of the number of cities in CITY having a Population larger than 100,000.

풀이

1
2
3
SELECT COUNT(*)
FROM city
WHERE population > 100000

Population Density Difference

  • Query the difference between the maximum and minimum populations in CITY.

풀이

1
2
SELECT MAX(population) - MIN(population)
FROM city

Weather Observation Station 4

  • Find the difference between the total number of CITY entries in the table and the number of distinct CITY entries in the table.
  • The STATION table is described as follows:

image

풀이

1
2
SELECT COUNT(city) - COUNT(DISTINCT city)
FROM station

Top Earners

  • 문제 바로가기
  • We define an employee’s total earnings to be their monthly salary * months 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.
    • salary와 months를 곱하여 earnings를 생성한다.
    • group by를 통해 최고 금액과 갯수를 구한다.

image

풀이

1
2
3
4
5
6
SELECT months * salary AS earnings
     , COUNT(*)
FROM employee
GROUP BY earnings
ORDER BY earnings DESC
LIMIT 1
0%