SQL 複数の行をまとめる(集約関数/group by/having)

SQLの複数の行をまとめるサンプルです。
集約関数とgroup by句とhaving句を使用します。

複数行をまとめる	集約関数
	複数の行をグループごとにまとめる(group by)
	集約関数の値に条件を指定する(having)
	グループごとの件数を表示する
	グループごとの件数を表示＋group byで指定した項目にnullがある
	グループごとではなく、グループの件数を表示する
	複数行を対象として最大値がある行を取得する

集約関数

集約関数は、複数の行をまとめて１つの列の平均や合計などの計算結果を返します。

主な集約関数
集約関数	説明
SUM()	合計を返す
AVG()	平均を返す
MAX()	最大値を返す
MIN()	最小値を返す
COUNT()	個数を返す

集約関数の使用例

次のテーブルがあるとします。

salesテーブル
id	name	price
1	りんご	300
2	みかん	200
3	白菜	200
4	玉ねぎ	100

SQL

上記テーブルに対して集約関数を使用してデータを抽出します。

SELECT SUM(price),AVG(price),MAX(price),MIN(price),COUNT(price) 
FROM sales;

1行目は、それぞれ集約関数を使用しています。

結果

結果は、以下のとおりです。

SUM(price)	AVG(price)	MAX(price)	MIN(price)	COUNT(price)
800	200	300	100	4

複数の行が1行になり集約関数の値が表示されます。

複数の行をグループごとにまとめる(group by)

select 項目 from テーブル名
group by 項目 [,項目]

group byで指定した項目で、複数の行をまとめます。
select句で指定する項目(※1)は、gourp byの項目として指定している必要があります。
→group byに項目があり、select句の後に項目がない場合は実行できます。

※1 集約関数と定数は除きます。

group byの使用例

次のテーブルがあるとします。

salesテーブル
shop	category	name	price
1	フルーツ	りんご	300
1	フルーツ	みかん	200
1	野菜	白菜	200
2	野菜	玉ねぎ	100

SQL

上記テーブルに対してgroup by句を使用してデータを抽出します。

SELECT category,AVG(price) 
FROM sales
group by category;

3行目は、group by句を指定しています。

結果

結果は、以下のとおりです。

category	AVG(price)
フルーツ	250
野菜	150

group by句で指定した項目「category」の値でまとめられた行が表示されます。
また、その行ごとに集約関数の値が表示されます。

group byの項目を複数にした場合

group byに項目のshopを追加した場合は、

SELECT shop,category,AVG(price) 
FROM sales
group by shop,category
order by shop,category;

shop,categoryの単位で行がまとまります。

shop	category	AVG(price)
1	フルーツ	250
1	野菜	200
2	野菜	100

集約関数の値に条件を指定する(having)

select 項目 from テーブル名
group by 項目
having 条件

having句を使用すると、複数の行をグループごとにまとめた列の集約関数の値に条件を指定できます。

havingの使用例

次のテーブルがあるとします。

salesテーブル
shop	category	name	price
1	フルーツ	りんご	300
1	フルーツ	みかん	200
1	野菜	白菜	200
2	野菜	玉ねぎ	100

SQL

上記テーブルに対してhaving句を使用してデータを抽出します。

SELECT category,AVG(price) 
FROM sales
group by category 
having AVG(price) > 200;

4行目は、having句を指定しています。

処理のイメージ

処理のイメージは、以下のとおりです。

１．group by句で指定した項目「category」の単位でまとめらます。

※途中の状態です
category	AVG(price)
フルーツ	250
野菜	150

２．AVG(price)の列に対してhaving句で指定した条件(200より大きい)を満たす行が表示されます。

category	AVG(price)
フルーツ	250

where句を使用した場合

以下のように、where句で、集約関数(AVG)の結果を条件にすることはできません。

SELECT category,AVG(price) 
FROM sales 
where AVG(price) > 200
group by category;

MySQLでは、以下のメッセージが表示されます。
#1111 - 集計関数の使用方法が不正です。

集約関数の結果を条件にする場合は、having句を使用します。

where句を使用できるパターン

ただし、集約関数の結果以外を条件にする場合は、group byとwhereを使用できます。

SELECT category,AVG(price) 
FROM sales 
where name = 'りんご'
group by category;

この場合は、件数を絞れるwhereを使用した方が良いです。having句は処理が遅くなります。

グループごとの件数を表示するサンプル

グループごとの件数を表示するサンプルです。よく使われます。

次のテーブルがあるとします。

salesテーブル
shop	category	name	price
1	フルーツ	りんご	300
1	フルーツ	みかん	200
1	野菜	白菜	200
2	野菜	玉ねぎ	100

SQL

上記テーブルに対してカテゴリ(category)ごとの件数を求めます。

select category,count(category) as cnt 
from sales
group by category;

1行目は、countでcategoryの件数を求めています。
3行目は、group byでcategory単位でまとめています。

結果

結果は、以下のとおりです。

category	cnt
フルーツ	2
野菜	2

カテゴリごとに、何件あるのかがわかります。

グループごとの件数を表示＋group byで指定した項目にnullがある

グループごとの件数を表示＋group byで指定した項目にnullがあるサンプルです。

次のテーブルがあるとします。

salesテーブル
shop	category	name	price
1	フルーツ	りんご	300
1	フルーツ	みかん	200
1		白菜	200
2		玉ねぎ	100

SQL

上記テーブルに対してカテゴリ(category)ごとの件数を求めます。

select category,count(category) as cnt 
from sales
group by category;

1行目は、countでcategoryの件数を求めています。

結果

結果は、以下のとおりです。

category	cnt
	0
野菜	2

カテゴリはnullで、件数は0件で表示されます。

ヒット件数が0件のとき

以下のように条件を指定してヒット件数が0件のときは、

select category,count(category) as cnt 
from sales
where shop> 10
group by category;

以下のようにデータ自体ありません。

category	cnt

グループごとではなく、グループの件数を表示する

グループごとではなく、グループの件数を表示するサンプルです。

次のテーブルがあるとします。
カテゴリの野菜、フルーツの2種類の2を抽出したい場合です。

salesテーブル
shop	category	name	price
1	フルーツ	りんご	300
1	フルーツ	みかん	200
1	野菜	白菜	200
2	野菜	玉ねぎ	100

SQL

上記テーブルに対してグループの件数を求めます。

select count(distinct category) as cnt from sales;

distinctでcategoryの重複行をまとめ、countでその件数を求めています。

結果

結果は、以下のとおりです。

cnt
2

カテゴリの件数がわかります。

nullの場合

categoryの値が全てnullのときに、上記のSQLを実行した場合は0が表示されます。

nullの行もカウントしたい

nullの行も1行としたいときは、nullをNVLで置き換えます(oracle)。

次のテーブルがあるとします。

salesテーブル
shop	category	name	price
1	フルーツ	りんご	300
1	フルーツ	みかん	200
1		白菜	200
2		玉ねぎ	100

SQL

上記テーブルに対してグループの件数を求めます。

select count(distinct NVL(category,'test')) as cnt from sales;

結果

結果は、以下のとおりです。

cnt
2

nullも1行としてカウントされます。

複数行を対象として最大値がある行を取得する

次のテーブルがあるとします。

idの単位でのrow_noが最大値の行を取得します。

Testテーブル

id	row_no	memo
1001	1
1001	2
1002	1
1002	2
1002	3

SQL

上記テーブルに対してgroup by句とmaxを使用してデータを抽出します。

select id,max(row_no) as row_no from Test
group by id

結果

結果は、以下のとおりです。

id	row_no
1001	2
1002	3