TIL 2021.07.20 | (SQL) Group by & Order by

私はまだSQLに詳しくありません.エラーや改善すべき点をいつでも残してください.🙏
●夏休み勉強SQL
SQL理論を学ぶため、SQL中→9月に中国SQL資格取得を目指します
オンライン学習SQL:スパルタコードクラブ

TIL 2021.07.20 start!

1.Group byカテゴリ統計の提供

1)Group byとは?
▶同じカテゴリのデータを組み合わせて、分類統計を行う
▶「~何人もいない」

select 범주별로 세어주고 싶은 필드명, count(*) from 테이블명
group by 범주별로 세어주고 싶은 필드명;

▶クエリ実行順:from→groupby→select
▶問合せの記入順序:

#1번
select * from users
group by name;

#2번 (완성)
select name, count(*) from users
group by name;

▶数字、文字列、時間の並べ替えが可能!
2)統計値
▶最小値:min(フィールド値)

select 범주가 담긴 필드명, min(최솟값을 알고 싶은 필드명) from 테이블명
group by 범주가 담긴 필드명;

▶最大値:max(フィールド値)

select 범주가 담긴 필드명, max(최댓값을 알고 싶은 필드명) from 테이블명
group by 범주가 담긴 필드명;

▶最低価格:avg(フィールド値)

select 범주가 담긴 필드명, avg(평균값을 알고 싶은 필드명) from 테이블명
group by 범주가 담긴 필드명;

▶総計:sum(フィールド値)

select 범주가 담긴 필드명, sum(합계를 알고 싶은 필드명) from 테이블명
group by 범주가 담긴 필드명;

2.整然と並ぶorder by

1)order byの使い方

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

2)昇順、降順
▶昇順:記入不要.(デフォルト)
▶降順:desc

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

▶クエリ実行順:from→groupby→select→order by

3.Whereセクションで使用するGroup by，Order by

1)Whereセクションの場所

#예시
select payment_method, count(*) from orders
where course_title = "웹개발 종합반"
group by payment_method;

▶groupbyとselectの間にwhere条件を入れて終了!
2)クエリの実行順序
from → where → group by → select

4.今日の練習💛

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

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, avg(likes) from checkins
group by week;

select week, round(avg(likes),2) from checkins
group by week; #round → 자릿수

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

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

#퀴즈1. 앱개발 종합반의 결제수단별 주문건수 세어보기

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

#퀴즈2.  Gmail을 사용하는 성씨별 회원수 세어보기

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

#퀴즈3. course_id별 '오늘의 다짐'에 달린 평균 like 갯수 구해보기 +자릿수 나타내기

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

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

select payment_method, count(*) from orders
where email like '%naver.com' and course_title = '앱개발 종합반'
group by payment_method

ジョブ>>

5.その他の文法

1)別名機能:Alias(Alias)
クエリーが長くなるにつれて、混同されることがよくあります.
したがって、SQLは別名Aliasをサポートします.
表示❗o→orders❗

select * from orders o
where o.course_title = '앱개발 종합반'

2)別名機能:as

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