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;