[스파르타/SQL] Group by ~, Select by ~

select ~ from ~, where ~에 부가적으로 사용하는

group by, select by을 배웠다 ;)

• min, max, avg, sum, round, ~as

엑셀이랑 비슷해서 어렵진 않았다.
1주일만에 다시 봤는데도 나름 수월히 해냈다.
뿌듯해. 얼른 SQL 마스터 하자 왕초보 탈출하자 ~

#단계별로 나아가는 group by. 그룹 먼저 주고 선택추출하기
#1
select * from users
group by name

#2
select name, count(*) from users
group by name

#별칭주기
select name, count(*) as cnt from users
group by name

#최소 min, 최대 max, 평균 avg, 합계 sum 소수점 round(~, 표시자리수)
select week, round(avg(likes), 1) as avg_likes from checkins
group by week

#정렬하기
select name, count(*) as cnt from users
group by name
order by cnt desc #내림차순 / 오름차순은 asc or 미표시

그리고 실습한 내용들

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

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

#검증
select count(*) from checkins
where week = 1

#min
select week, min(likes) from checkins #주차별 최소 likes
group by week

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

#avg
select week, avg(likes) from checkins
group by week

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

select week, round(avg(likes), 1) as avg_likes from checkins
group by week

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

#검증
select * from checkins
where week = 3

#order BY 
select name, count(*) from users
group by name
order by count(*) #작은 것부터

select name, count(*) from users
group by name
order by count(*) desc #큰 것부터 desc <-> asc

select * from checkins
order by likes desc

#웹개발 종합반의 결제수단별 주문건수 세어보기
select payment_method, count(*) from orders
where course_title = '웹개발 종합반'
group by payment_method 
order by count(*)

#select payment_method, count(*) from orders
#group by payment_method 

select * from users
order by updated_at desc

#퀴즈 - 앱개발 종합반의 결제수단별 주문건수
select course_title, payment_method, count(*) from orders
where course_title = '앱개발 종합반'
group by payment_method
order by count(*) desc

#퀴즈 - g메일 사용하는 성씨별 회원수
select name, count(*) from users
where email like '%gmail.com'
group by name
order by count(*) desc

#퀴즈 - 코스아이디별 오늘의 다짐에 달린 평균 like 개수 구하기
select course_id, ROUND(avg(likes),1) from checkins
group by course_id 

#select * from orders
#where email like '%g'
#group by ~ 후에, select 수정하기.

#alias 알리아스
select * from orders o #orders를 이제 o라고 부를게
where o.course_title = '앱개발 종합반' #o.

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

#숙제 - 네이버 이메일 사용해서 앱개발 종합반을 신청한 주문의 결제수단별 주문건수
select course_title, payment_method, count(*) as count from orders
where email like '%naver.com' 
	and course_title = '앱개발 종합반'
group by payment_method 
order by count asc

근데 스크립트 새로 만드는 방법을 모르겠다
한 데이터 베이스당 하나만 가질 수 있는 건가
새로 만들 때마다 오류 뜬다
아주 고냥 ~~ 패쓰 !