2022/1/12

group by

select name, count(*) from users
group by name;

select week, count(*) from checkins
group by week;

 

min - 최솟값

select week, min(likes) from checkins 
group by week

 

max - 최댓값

select week, max(likes) from checkins
group by week

 

avg - 평균 / round - 반올림

select week, round(avg(likes), 2) from checkins
group by week;

 

sum - 합계

select week, sum(likes) from checkins
group by week

 

---

 

order by - 정렬(정렬은 마지막에 한다) 아무것도 안붙이면 오름차순(asc), 내림차순은 desc

select name, count(*) from users
group by name
order by count(*);

select * from checkins
order by likes desc;

 

실행 순서 : from → group by → select → order by

select name, count(*) from users
group by name
order by count(*);

 

where 절과 함께 사용하기

select payment_method, count(*) from orders
where course_title = '웹개발 종합반'
group by payment_method
order by count(*);

 

 

---

 

order by , group by 연습

• 앱개발 종합반의 결제수단별 주문 건수

select payment_method, count(*) from orders
where course_title = '앱개발 종합반'
group by payment_method;

• gmail 을 사용하는 성씨별 회원수

select name, count(*) from users
where email like '%gmail.com'
group by name;

• course_id 별 ‘오늘의 다짐’에 달린 평균 like 갯수

select course_id, round(avg(likes), 1) from checkins
group by course_id;

 

 

---

 

별칭

select payment_method, count(*) as cnt from orders o
where o.course_title = '앱개발 종합반'
group by payment_method

 

---

 

숙제

네이버 이메일을 사용해 앱개발 종합반을 신청한 주문의 결제 수단별 주문건수 세어보기

select payment_method, count(*) from orders
where email like '%naver.com'
group by payment_method;