count

경찰서 총 개수 세기(중복 빼야겠지)

select count(distinct police_station) from crime_status

sum

종로, 남대문 경찰서에서 발생한 강도 사건의 합

select sum(case_number) from crime_status where police_station in ('종로', '남대문') and crime_type = '강도';

폭력 사건 중 검거된 사건의 합

select sum(case_number) from crime_status where crime_type = '폭력' and status_type ='검거';

avg

select avg(case_number) from crime_status where crime_type like '폭력';

select avg(case_number) from crime_status where crime_type like '살인' and status_type like '발생';

select avg(case_number) from crime_status where police_station like '서초' and status_type like '검거';

select avg(case_number) from crime_status where police_station in ('구로','도봉') and status_type like '검거' and crime_type like '살인';

min

select min(case_number) from crime_status where police_station like '광진' and status_type like '검거';

max

select max(case_number) from crime_status where crime_type like '절도' and status_type like '검거';

확인
select police_station, crime_type, case_number from crime_status where crime_type like '절도' and status_type like '검거' order by case_number desc;

group by + having

group 으로 묶어서 보여줌.
집계함수에 조건을 넣을 때 having

select police_station, sum(case_number) count from crime_status where status_type like '발생' group by police_station having count >4000

select police_station, avg(case_number) count from crime_status where crime_type in('폭력', '절도') and status_type like '발생' group by police_station having count >= 2000 order by count desc ;

음.. 15-11 2번 문제에서
가장 많은 범죄의 crime_type을 같이 뽑고 싶은데 어떻게 해야할까
서브쿼리 같은데

GPT는 역시 서브쿼리 이용했다

SELECT 
    cs1.police_station, 
    cs1.case_number, 
    cs1.crime_type 
FROM 
    crime_status cs1
JOIN 
    (SELECT 
        police_station, 
        MAX(case_number) AS max_case_number
     FROM 
        crime_status 
     GROUP BY 
        police_station) cs2
ON 
    cs1.police_station = cs2.police_station