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

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

公開日 : 2014年5月10日 / 更新日 : 2017年12月29日

 
 

初級編

 
 

 
 

 
 

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

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

目次 [非表示]

集計関数

説明

SUM

合計を求める

AVG

平均を求める

MAX

最大値を求める

MIN

最小値を求める

COUNT

個数(行数)をカウントする

合計を求める ~ SUM

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

1

2

SELECT
SUM(給料)

FROM Table_Syain

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

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


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

平均を求める ~ AVG

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

1

2

SELECT
AVG(給料)

FROM Table_Syain


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

最大値を求める ~ MAX

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

1

2

SELECT
MAX(給料)

FROM Table_Syain


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

最小値を求める ~ MIN

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

1

2

SELECT
MIN(給料)

FROM Table_Syain


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

個数(行数)をカウントする ~ COUNT

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

1

2

SELECTCOUNT(*)

FROM Table_Syain


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


集計対象がNULLを含む場合

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

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


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


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


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


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

1

2

3

4

5

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を指定していなかったのですが、そのまま「給料が最大の社員の社員番号と名前と給料」を取得してみたいと思います。

1

2

3

4

5

SELECT 社員番号, 名前, MAX(給料)

FROM Table_Syain

——————————————

メッセージ 8120、レベル 16、状態 1、行 1

‘Table_Syain.社員番号’
は選択リスト内では無効です。この列は集計関数または GROUP
BY 句に含まれていません。


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

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


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

1

2

SELECT
SUM(給料)

FROM Table_Syain


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

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


GROUP BY の書き方

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

1

2

3

SELECT 社員番号, 名前, SUM(給料)

FROM Table_Syain

GROUP
BY 社員番号, 名前


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

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

1

2

3

SELECT 性別, AVG(年齢)

FROM Table_Syain

GROUP
BY 性別


 
 


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

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

HAVING の書き方

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

1

2

3

4

SELECT 性別, AVG(年齢)

FROM Table_Syain

GROUP
BY 性別

HAVING
AVG(年齢) < 30


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

1

2

3

4

5

6

7

SELECT 性別, AVG(年齢)

FROM Table_Syain

GROUP
BY 性別

HAVING 年齢 < 30

———————————————————-

メッセージ 8121、レベル 16、状態 1、行 4

‘Table_Syain.年齢’
HAVING 句内では無効です。この列は集計関数または GROUP
BY 句に含まれていません。


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

 
 

貼り付け元 <https://kaya-soft.com/sqlserver2008-toranomaki/beginner/beginner/groupby/>

 
 

  • Pocket
  • LINEで送る
  • このエントリーをはてなブックマークに追加

powered by Free WordPress Theme Principle

Copyright © ディズニー好きなコンビニ経営者ブログ All Rights Reserved.

Menu

HOME

TOP

テキストのコピーはできません。