SQL) 집계함수(Aggregate Functions)

COUNT

SELECT COUNT(column)
FROM tablename
WHERE condition;​

SUM

SELECT SUM(column)
FROM tablename
WHERE condition;

AVG

SELECT AVG(column)
FROM tablename
WHERE condition;

MIN, MAX

SELECT MIN|MAX(column)
FROM tablename
WHERE condition;

GROUP BY

SELECT column1, column2, ...
FROM table
WHERE condition
GROUP BY column1, column2, ...
ORDER BY column1, column2, ...;

🔔 crime_status에서 경찰서별로 그룹화하여 경찰서 이름을 조회

SELECT police_station
FROM crime_status
GROUP BY police_station
ORDER BY police_station;

💥 DISTINCT를 통해서 경찰서 종류 검색도 가능하나, ORDER BY 할 수 없음

SELECT DISTINCT police_station FROM crime_status;

 

🔔 경찰서별 평균 범죄 검거 건수 검색

SELECT police_station, avg(case_number) 평균검거건수
FROM crime_status
WHERE status_type LIKE '검거'
GROUP BY police_station
ORDER BY 평균검거건수 DESC

🔔 경찰서별 평균 범죄 검거 건수, 평균 범죄 검거 건수 검색

SELECT police_station, status_type, avg(case_number)
FROM crime_status
GROUP BY police_station, status_type;

HAVING : 조건에 집계함수가 포함되는 경우 WHERE 대신 HAVING 사용

SELECT column1, column2, ...
FROM table
WHERE condition
GROUP BY column1, column2, ...
HAVING condition (Aggregate Functions)
ORDER BY column1, column2, ...

🔔 경찰서별 발생한 범죄 건수의 합이 4000건보다 큰 경우 검색

SELECT police_station, sum(case_number) count
FROM crime_status
WHERE status_type LIKE '발생'
GROUP BY police_station
HAVING count > 4000;