[SQL] group by, order by, min, max, sum. round, count

 

 

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, sum(likes) from checkins
group by WEEK;
SELECT WEEK, round(avg(likes),1) from checkins
group by WEEK;

Order by를 사용하면 한 번에 정렬할 수 있어요.

SELECT name, count(*) FROM  users u 
group by name
order by count(*) desc;

SELECT * FROM  checkins
order by likes desc;

 

 

[연습하기]

- [퀴즈] 앱개발 종합반의 결제수단별 주문건수 세어보기
SELECT course_title,payment_method, count(*) FROM orders o 
WHERE course_title = '앱개발 종합반'
GROUP by payment_method;
  
        
- [퀴즈] Gmail 을 사용하는 성씨별 회원수 세어보기
SELECT  name, count(*) FROM users
WHERE email like '%gmail.com'
GROUP by name 
order by name;
        
- [퀴즈] course_id별 '오늘의 다짐'에 달린 평균 like 개수 구해보기
SELECT course_id, ROUND(avg(likes),2)  FROM  checkins c 
group by course_id;