Webサイト制作コースのお申し込みはこちら Webサイト制作コースのお申し込みはこちら

SQLのgroup byのサンプルコードを集めてみました。

グルーピングして集計したり、グループ内の最大値、最小値を求める際に便利です。

以下、データベースとして、MySQLのサンプルデータベースEmployeesを使っています。SQL実行結果の表示にはphpMyAdminを使用しています。

group byで集計するサンプルコード

1つの項目を使って、group byで集計する例

部署番号(dept_no)と、社員番号(emp_no)を格納したテーブル、dept_empを使って集計してみましょう。

部署ごとの社員数を抽出してみましょう。

dept_noごとのレコードの数をgroup byで集計することで求められます。なお、在籍中の社員のみ対象とするために、to_date(在籍期間終了日)が”9999-01-01″の社員のみという条件を指定します。

SELECT 
	dept_no, 
	count(*) 
FROM 
	dept_emp 
WHERE 
	to_date = "9999-01-01" 
group by 
	dept_no

結果はこうなります。並びは、dept_no順にソートされます。

結果

group byの集計をjoin(結合)する例

せっかく集計したのですが、どの部署に何人いるのかわかりにくいので、テーブルdepartmentsと結合して、部署名(dept_name)を表示させましょう。

SELECT 
	departments.dept_name, 
	count(*)
FROM 
	dept_emp 
left join departments
ON dept_emp.dept_no = departments.dept_no
WHERE 
	to_date = "9999-01-01" 
group by 
	departments.dept_name

結果はこうなります。並びは、dept_nameでソートされます。

結果

likeを使ったワイルドカード指定のgroup by集計の例

テーブルtitles(肩書)を使って、ワイルドカード指定でgroup by集計をしてみましょう。

それぞれの肩書を持つ人数を抽出してみます。

titlesテーブルには、社員の過去の肩書も含まれていて、to_dateが”9999-01-01″のものが最新の肩書になります。

まず、単純にtitleで集計するとこうなります。

SELECT 
	title, 
	count(*)
FROM 
	titles 
WHERE 
	to_date="9999-01-01"
group by
	title

結果はこうなります。

結果

「Assisttant Engineer」と「Engineer」と「Senior Engineer」、「Staff」と「Senior Staff」を、それぞれ一つにまとめて集計してみましょう。

それぞれlikeで”%Engineer%”、”%Staff%”とワイルドカード指定すればいけそうですね。

SELECT 
	CASE
		WHEN title like '%Engineer%' THEN 'Engineer'
		WHEN title like '%Staff%' THEN 'Staff'
		ELSE title
	END as Katagaki
	,
	count(*)
FROM 
	titles 
WHERE 
	to_date="9999-01-01"
group by
	Katagaki

結果はこうなります。

結果

CASEは、項目に条件分岐を含むプログラム処理的な動きをさせることができます。

group byでmax、min(最大最小)・平均・合計を求める例

salaries(給与)テーブルを使って、部署ごとに、もっとも高い給料をもとめてみましょう。

salariesテーブルには、各社員の過去の時点の給与も含まれているため、to_date=”9999-01-01″の、最新の給与のみ対象とします。

また、部署名を表示させるため、emp_dept(社員番号と部署番号テーブル)、departments(部署テーブル)を結合します。

SELECT 
	departments.dept_name as bushomei, 
	max(salary) as max_kyuuryo
FROM 
	salaries 
	left join dept_emp ON dept_emp.emp_no = salaries.emp_no
	left join departments ON departments.dept_no = dept_emp.dept_no
WHERE
	salaries.to_date="9999-01-01"
group by
	bushomei

結果はこうなります。もっとも給与が高い社員がいるのは、Sales(営業部)でした。

同様に、「 max(salary) as max_kyuuryo 」の部分を変更すれば、平均、最小、合計を求めることができます。

group byにminを組み合わせた例

minを使って、各部署の給与がもっとも安い人を求めました。

min(salary) as min_kyuuryo

各部署とも、それほど大きな差はないようです。

group byにavgを組み合わせた例

avgを使って、各部署の平均給与を求めました。

avg(salary) as avg_kyuuryo

平均で見ても、営業部の給料は高め。

group byにsumを組み合わせた例

sumで各部署の給与合計を求めました。1年に支払われる給与合計がわかります。

sum(salary) as sum_kyuuryo

人数が多いため、Development(開発)部署の給与合計がもっとも高くなりました。

group byで最新のレコードを求める例

dept_emp(社員番号、部署番号の紐付け用テーブル)を使って、各部署の一番の新入社員を求めてみましょう。

日付データに対してminとgroup byを組み合わせることで、グループ内の最新日付のデータを抽出できます。

SELECT 
	departments.dept_name, 
	employees.first_name,
	employees.last_name,
	max(dept_emp.from_date)
FROM 
	dept_emp 
	left join departments ON departments.dept_no = dept_emp.dept_no
	left join employees ON dept_emp.emp_no = employees.emp_no
WHERE 
	to_date="9999-01-01"
GROUP BY
	departments.dept_name

結果はこうなります。

group byの集計結果をhavingで絞り込む例

 

先ほど、group byで部署別の給与平均を出した例を発展させて、「部署別の平均給与(7万ドル超え限定)」を抽出してみます。

SELECT 
	departments.dept_name as bushomei, 
	avg(salary) as avg_kyuuryo
FROM 
	salaries 
	left join dept_emp ON dept_emp.emp_no = salaries.emp_no
	left join departments ON departments.dept_no = dept_emp.dept_no
WHERE
	salaries.to_date="9999-01-01"
group by
	bushomei
having
	avg_kyuuryo > 70000

結果はこうなりました。

結果

group by に続けて、havingに条件を指定すると絞り込みができるんですね。

「whereは、集計前の絞り込み条件」「havingは、集計後の絞り込み条件」を指定する、と覚えておくとよいでしょう。

日付範囲指定でgroup by集計する例

employeesテーブルから、年ごとの新入社員数を求めてみましょう。

SELECT 
	count(*) as shinnyushain, 
	date_format(hire_date,'%Y') as year 
FROM 
	`employees` 
group by
	year

結果はこうなります。

結果

徐々に新入社員の数が減って、なんと2000年には13人。

date_format(hire_date,’%Y’) as yearの箇所を、以下のように変更することで、年月別の集計が可能になります。

date_format(hire_date,'%Y-%M') as year

結果

エンジニアになりたい人に選ばれるプログラミングスクール「ポテパンキャンプ 」

ポテパンキャンプは卒業生の多くがWebエンジニアとして活躍している実践型プログラミングスクールです。 1000名以上が受講しており、その多くが上場企業、ベンチャー企業のWebエンジニアとして活躍しています。

基礎的な学習だけで満足せず、実際にプログラミングを覚えて実践で使えるレベルまで学習したいという方に人気です。 プログラミングを学習し実践で使うには様々な要素が必要です。

それがマルっと詰まっているポテパンキャンプでプログラミングを学習してみませんか?

卒業生の多くがWebエンジニアとして活躍

卒業生の多くがWeb企業で活躍しております。
実践的なカリキュラムをこなしているからこそ現場でも戦力となっております。
活躍する卒業生のインタビューもございますので是非御覧ください。

経験豊富なエンジニア陣が直接指導

実践的なカリキュラムと経験豊富なエンジニアが直接指導にあたります。
有名企業のエンジニアも多数在籍し品質高いWebアプリケーションを作れるようサポートします。

満足度高くコスパの高いプログラミングスクール「ポテパンキャンプ」

運営する株式会社ポテパンは10,000人以上のエンジニアのキャリアサポートを行ってきております。
そのノウハウを活かして実践的なカリキュラムを随時アップデートしております。

代表の宮崎もプログラミングを覚えサイトを作りポテパンを創業しました。
本気でプログラミングを身につけたいという方にコスパ良く受講していただきたいと思っておりますので、気になる方はぜひスクール詳細をのぞいてくださいませ。