【初級編⑪】SQLのGROUP BYでレコードのグループ化と集計を行う

これまでの記事で、基本的なSELECT文の実行はマスターできたんじゃないでしょうか。今回からは、少し高度な内容に踏み込んで行きたいと思います。

まずは、業務でよく利用する機能として「集計機能」を説明したいと思います。例えば商品別の販売個数、一日の売上合計など、業務を行う中で様々な集計がされています。集計をする為には、何をキーに集計するかが重要です。

1 SQLで集計を行う
2 集計対象のキーを指定する　～ GROUP BY ～
- 2.1 GROUP BYがなぜ必要か
- 2.2 GROUP BY の書き方
3 集計結果に対する条件指定　～ HAVING ～
- 3.1 HAVING の書き方
4 この記事と関連性の高い記事

SQLで集計を行う

SQLで集計を行うには、「集計関数」と呼ばれる関数を実行します。Excelの関数と似ていて、名前からも推測しやすいものばかりです。集計関数は、SELECT句の中で直接記述します。

集計関数の種類

中でもよく利用する集計関数を表に纏めるとこんな感じです。どれもExcelでもよく使うものですね。

集計関数	説明
SUM	合計を求める
AVG	平均を求める
MAX	最大値を求める
MIN	最小値を求める
COUNT	個数（行数）をカウントする

合計を求める　～ SUM ～

合計を求めるには、SUM関数を使用します。社員マスタに対して、社員全員の給料の合計を求める場合、次のようなSELECT文を実行します。

SELECT SUM(給料)
FROM Table_Syain

関数の使い方について、詳細は【初級編⑦】SELECT文で関数を使いこなすをご覧ください。ここでは給料をSUMしたいので、SUM()のカッコの中に集計対象である「給料」を指定します。

集計する前の全件と比較してみましょう。

確かに、社員マスタ全件の給料の合計が算出できていますね。

平均を求める　～ AVG ～

平均を求めるには、AVG関数を使用します。AVGは、Averageの略です。社員の給料の平均を求める場合、先ほどの「SUM」が「AVG」に変わるだけです。

SELECT AVG(給料)
FROM Table_Syain

全社員の給料の平均が算出されていることが分かります。

最大値を求める　～ MAX ～

最大値を求めるには、MAX関数を使用します。MAXは、MAXIMUMの略です。

SELECT MAX(給料)
FROM Table_Syain

全社員の給料の平均が算出されていることが分かります。

最小値を求める　～ MIN ～

最小値を求めるには、MIN関数を使用します。MINは、MINIMUMの略です。

SELECT MIN(給料)
FROM Table_Syain

全社員の給料の平均が算出されていることが分かります。

個数（行数）をカウントする　～ COUNT ～

COUNTは、個数（行数）をカウントする集計関数です。集計関数では、これまでの「給料」の最大値や平均値を求めてきたように、集計対象となる列を()の中に指定しました。COUNT関数の場合「レコードの行数」をカウントする関数ですので、どの列を指定してもいいので、こういう場合「*」を指定します。

SELECT　COUNT(*)
FROM Table_Syain

ちなみに、「COUNT(給料)」としても、給料の値が加算されるわけではありませんのでご注意を。それはSUMです。

集計対象がNULLを含む場合

これまで、給料の値を集計してきましたが、集計値の中にNULLを含んでいる場合集計の対象外となるので注意が必要です。そもそも給料とか数値にNULLを指定するのはどうかってのはありますが。

これを試す為に、社員マスタの「給料」列をNULL許容に変えておきます。

社員番号「00008」の「給料」をNULLにしておきます。

「給料」にNULLが存在する状態で集計をした場合、SUMやMAX、MINなら問題ないのですが、AVG関数を実行した場合今まではレコードは８件あるので合計を８で割っていましたが、NULLのレコードは対象外となり７で割った値が算出されます。

また、COUNT関数を使用した場合も「給料」列を指定した場合はNULLのレコードは無視されますが、[*]を指定して全列指定した場合はレコード件数をカウントできます。

値が[0]であればもちろん集計されますので、集計対象がNULLを許容する列の場合は「IS NULL」をしておく方が良いです。

SELECT AVG(給料)				AS 'AVG(給料)'
	  ,AVG(ISNULL(給料, 0))	AS 'AVG(給料)'
	  ,COUNT(ISNULL(給料, 0))	AS 'COUNT(給料)'
	  ,COUNT(*)				AS 'COUNT(*)'
FROM Table_Syain

集計対象のキーを指定する　～ GROUP BY ～

今までは、単純に合計値や平均値だけを求めていましたが、例えば「給料が最大である社員の社員番号と名前」を取得したい場合などでは、「社員番号と名前」をキーに集計する必要があります。この集計対象のキーを指定するのが「GROUP BY句」になります。

GROUP BYがなぜ必要か

今までGROUP BYを指定していなかったのですが、そのまま「給料が最大の社員の社員番号と名前と給料」を取得してみたいと思います。

SELECT 社員番号, 名前, MAX(給料)
FROM Table_Syain
------------------------------------------
メッセージ 8120、レベル 16、状態 1、行 1
列 'Table_Syain.社員番号' は選択リスト内では無効です。この列は集計関数または GROUP BY 句に含まれていません。

何やらエラーが出ましたね。これは何故かと言うと、集計するということはもともと複数行あったとしても集計結果は基本的に１行になるから、もともとあったレコードのどのレコードの値を取得すれば良いか分からないからです。

文章で説明すると分かりにくいので図で説明します。まず、社員マスタには8レコード存在していますので、社員番号や名前、給料をSELECTすると8レコード全て取得されます。

そこで、給料の合計値を求めてみましょう。このSQLは先ほどからエラーなく実行できていました。

SELECT SUM(給料)
FROM Table_Syain

この結果からも分かるように、基本的に集計すると結果の1レコードだけになるのですが、それは全社員8レコードを全部一括りに集計したからなのです。また、この[1915000]という給料はどの社員のデータでもないので、社員番号や名前を取得しようがないのです。

Excelで表を作った時のことをイメージすると分かりやすいと思います。

GROUP BY の書き方

集計処理を行う時に、集計を行う列はGROUP BY句に書かなくてもSELECTできますが、集計をしない列をSELECTするには必ずGROUP BY句に書く必要があります。GROUP BYは、FROM句の後ろに書き、複数のキーを指定する場合、カンマで区切ります。

SELECT 社員番号, 名前, SUM(給料)
FROM Table_Syain
GROUP BY 社員番号, 名前

こうすることで、社員番号と名前をSELECTできました。ただ、「社員番号と名前毎に集計する」ことになるので、それぞれの社員番号と名前でレコードは１件しか無いので集計する前と結果は同じです。

集計とは、例えば毎日の売上明細を商品毎に集計する場合や、日次、月次で集計するような使い方になります。社員マスタの場合、「性別毎の平均年齢」とかなら集計できますね。

SELECT 性別, AVG(年齢)
FROM Table_Syain
GROUP BY 性別

集計結果に対する条件指定　～ HAVING ～

テーブルから取得するレコードを絞り込む時に使うのは「WHERE」でしたが、集計結果に対して条件を指定するには「HAVING」を使用します。書き方はWHEREとほとんど同じなのですが、あくまでSUMやAVGした結果に対しての条件指定になります。

HAVING の書き方

HAVINGは、GROUP BY句の後ろに続けて書きます。書き方は、先ほどの例で言うと「平均年齢が30才未満の性別」という風になります。

SELECT 性別, AVG(年齢)
FROM Table_Syain
GROUP BY 性別
HAVING AVG(年齢) < 30

「HAVING 年齢 < 30」としないように注意です。繰り返しになりますがHAVING句は集計結果に対して指定する条件です。

SELECT 性別, AVG(年齢)
FROM Table_Syain
GROUP BY 性別
HAVING 年齢 < 30
----------------------------------------------------------
メッセージ 8121、レベル 16、状態 1、行 4
列 'Table_Syain.年齢' は HAVING 句内では無効です。この列は集計関数または GROUP BY 句に含まれていません。

集計やHAVINGは、頭の中で結果をイメージしながらSQLを書くことになるので慣れるまでは苦労しますが、業務ではほんとによく使う機能なのでなんとしてもマスターしましょう。

SQLで集計を行う

集計関数の種類

合計を求める ～ SUM ～

平均を求める ～ AVG ～

最大値を求める ～ MAX ～

最小値を求める ～ MIN ～

個数（行数）をカウントする ～ COUNT ～

集計対象がNULLを含む場合

集計対象のキーを指定する ～ GROUP BY ～

GROUP BYがなぜ必要か

GROUP BY の書き方

集計結果に対する条件指定 ～ HAVING ～

HAVING の書き方

この記事と関連性の高い記事

関連記事

コメントを残す コメントをキャンセル

合計を求める　～ SUM ～

平均を求める　～ AVG ～

最大値を求める　～ MAX ～

最小値を求める　～ MIN ～

個数（行数）をカウントする　～ COUNT ～

集計対象のキーを指定する　～ GROUP BY ～

集計結果に対する条件指定　～ HAVING ～

コメントを残すコメントをキャンセル