-집계 함수
count : 총 갯수 계산
sum : 합계를 계산
avg : 평균을 계산
min : 가장 작은값 찾음
max : 가장 큰 값 찾음
first : 첫번째 결과값을 리턴
last : 마지막 결과값을 리턴
-count
select count(~) from 테이블 (where);
ex)select count(distinct police_station) from crime_status;
-sum
select sum(칼럼) from 테이블 (where);
ex) select sum(case_number) from crime_status where crime_type = '강도';
-avg
select avg(칼럼) from 테이블 (where);
ex) select avg(case_number) from crime_status where crime_type like '폭력' AND status_type = '검거';
-min
select min(칼럼) from 테이블 (where);
ex) select min(case_number) from crime_status where crime_type LIKE '강도' AND status_type = '발생';
-max
select max(칼럼) from 테이블 (where);
ex) select max(case_number) from crime_status where crime_type Like '살인';
-group by
select 칼럼 from 테이블 where ~ group by 칼럼1,2~ ORDER BY 칼럼1,2~
ex) select police_station from crime_status group by police_station order by police_station;
ex)select police_station ,sum(case_number) 발생건수 from crime_status
where status_type LIKE '발생' group by police_station order by 발생건수 desc;
ex)select police_station , status_type,avg(case_number) from crime_status
group by police_station,status_type;
#예상되는 결과값은 police_station마다 status_type은 '발생'or'검거'이기 때문에 중부+발생 , 중부+검거 경찰서별로 2개씩 나올것이다.
-having
조건에 집계함수가 포함되면 where을 사용못해서 having 사용함
ex) select police_station,sum(case_number) count from crime_status
where status_type LIKE '발생' group by police_station HAVING count > 4000;
#sum을 사용해서 Having을 사용함
