2-1 2주차 배울 것
- 최대 / 최소 / 평균 / 갯수
- group by / order by
2-2 group by
select name, count(*) from users group by name
- count(*)[group by] : 필드명으로 묶은(group by)것 당 갯수
select name, count(*) from users where email like '%naver.com' group by name
2-3 group by 기능
select week, count(*) from checkins group by week
select week, min(likes) from checkins group by week
select week, max(likes) from checkins group by week
select week, round(avg(likes),2) from checkins group by week
select week, sum(likes) from checkins group by week
- round( ,숫자) : 소숫점 아래 숫자 자리 까지 반올림
- min / max / avg / sum (필드명)
2-3 order by
select name, count(*) from users group by name order by count(*) desc
- 오름차순 : 없음 또는 asc (order by 필드명)
- 내림차순 : desc (order by 필드명 desc)
- order by 는 group by 한 후 마지막에 (필드명 보고)
- order by 는 group by 없이도 쓸 수 있다.
select * from checkins order by likes desc
select payment_method, count(*) from orders where course_title = '웹개발 종합반' group by payment_method order by count(*) desc
2-4 주의사항
select * from orders group by payment_method
- 뭘 통계내라고 하는지 지정 안함 (대표적으로 하나씩만 보여줌)
- 범주(payment_method)가 안들어가도 값이 나옴 (select count(*) from ~)
2-5 연습하기
- 문자열 기준으로 정렬 가능
select * from users order by email desc - 시간을 기준으로 정렬 가능
select * from users order by created_at desc
select payment_method, count(*) from orders where course_title = '앱개발 종합반' group by payment_method
select name, count(*) from users where email like '%gmail.com' group by name
select course_id, round(avg(likes),2) from checkins group by course_id
2-6 별칭
- 별칭 : Alias (알리아스)
select * from orders o where o.course_title = '앱개발 종합반'
select payment_method, count(*) as cnt from orders o where o.course_title = '앱개발 종합반' group by payment_method
