7장_데이터 집계
GROUP BY 절
- WHERE 절과 ORDER BY절 사이에 위치
SELECT station_name
FROM subway_statistics
WHERE gubun = '승차'
GROUP BY station_name
ORDER BY station_name;
집계 함수
- COUNT( expr )
- 개수 반환
- MAX( expr )
- 최댓값 반환
- MIN( expr )
- 최솟값 반환
- SUM( expr )
- 합계 반환
- AVG( expr )
- 평균값 반환
- VARIANCE( expr )
- 분산 반환
- STDDEV( expr )
- 표준편차 반환
연습
- 6번째 항목 내림차순
SELECT station_name
,boarding_time
,gubun
,MIN(passenger_number) min_value
,MAX(passenger_number) max_value
,SUM(passenger_number) sum_value
FROM subway_statistics
GROUP BY station_name, boarding_time, gubun
ORDER BY 6 DESC;
HAVING 절
- GROUP BY 절과 함께 사용
- 함수 결과 값으로 조건을 걸 때 사용
SUM(passenger_number) 값이 15000~16000 사이인 값만 조회
SELECT station_name
,boarding_time
,gubun
,MIN(passenger_number) min_value
,MAX(passenger_number) max_value
,SUM(passenger_number) sum_value
FROM subway_statistics
GROUP BY station_name, boarding_time, gubun
HAVING SUM(passenger_number) BETWEEN 15000 AND 16000
ORDER BY 6 DESC;
DISTINCT
- 중복 값을 제외한 고유한 값들만 조회
SELECT DISTINCT station_name
FROM subway_statistics
WHERE gubun = '승차'
ORDER BY 1;