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

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

目次

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

集約関数

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

主な集約関数
集約関数 説明
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句を指定しています。

処理のイメージ

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

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

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

2.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

関連の記事

SQL distinct 重複行を表示しないサンプル
SQL LEFT JOIN 外部結合のサンプル
SQL 副問合せのサンプル(サブクエリ)

△上に戻る