zukucode
主にWEB関連の情報を技術メモとして発信しています。

SQL GROUP BYで複数の行を集計して1行で取得する

SQLでデータを取得するときに、複数行のデータを集計してデータの合計、平均、件数、最大値などを取得したい場合があります。

以下のようなテーブルがあるとします。

employee(社員)
idfirst_namelast_namedepartment_idheight
1一郎山田1170
2次郎佐藤2175
3三郎田中1185
4四郎鈴木2155

部署ごと(department_idごと) の平均身長、最大身長を取得したいときは以下のようなSQLになります。

SELECT
  department_id,
  AVG(height) AS avg_height,
  MAX(height) AS max_height
FROM
  employee
GROUP BY
  department_id
取得結果
department_idavg_heightmax_height
1177.5185
2165175

部署ごとのデータなので、同一のdepartment_idのデータは1行に集約して表示する必要があります。

GROUP BY 列名で集約したい列を指定します。上記例の場合はdepartment_idごとに集約したいのでdepartment_idを指定しています。

集計関数

GROUP BYで指定した列以外の列をSELECT句で取得したい場合は、集計関数で集計する必要があります。

なぜなら、以下の2つのデータはdepartment_idが同じなので1行に集約する必要があるわけですが、department_id以外の列の値は何を出力すればいいのかわからないからです。

GROUP BYで指定した列は同じ値のデータが集約される(集約されるデータの値はすべて同じ)ので、集計関数で集計する必要はありません。

employee
idfirst_namelast_namedepartment_idheight
1一郎山田1170
3三郎田中1185
集約結果
idfirst_namelast_namedepartment_idheight
???1?

集約されるデータの中での最大のデータを取得したい場合はMAX(列名)を指定します。

同じように、最小値はMIN、平均値はAVG、件数はCOUNTを使用します。

上記例では平均値と最大値を取得したいので、AVGMAXを使用しています。

集計関数で取得した列名は別名を使用する

SELECT句で集計関数で取得する項目に別名を指定していないと取得した結果に対してアクセスするときに困ります。

SELECT
  department_id,
  AVG(height),
  MAX(height)
FROM
  employee
GROUP BY
  department_id

集計関数を使用した列(AVGMAXの列)はアクセスするときに、列名がわからないのでどのようにアクセスすればいいのかがわからないためです。

データベースによっては自動的に列名を付与しますが、どのような列名になるのかがわからないので列名を指定してアクセスできません。

仮にheightとアクセスした時にAVGMAXのどちらの列のことなのかがわかりません。

そのため、集計関数を使用した場合は列名を指定する必要があります。

GROUP BYで複数列を指定する

複数の列を指定した場合は、指定した列すべての値が同一のデータが集約されます。

集計関数で集計して取得するか、それとも集計単位としてGROUP BYに指定するか、臨機応変に使い分けていく必要があります。


関連記事

  • Oracle 文字列の中で指定した文字を削除する

    Oracleで文字列の中で指定した文字を削除する方法を紹介します。REPLACEを使用して削除したい文字をNULL(空文字)に置き換えることで削除します。Oracleは空文字をNULLとして扱うので、...


  • SQL GROUP BYで複数の行を集計して1行で取得する

    GROUP BY 列名で集約したい列を指定します。上記例の場合は部署ごとに集約したいのでdepartment_idを指定しています。GROUP BYで指定した列以外の列をSELECT句で取得したい場合...


  • SQL FROM句で複数のテーブルを結合するポイント

    FROM句では取得したいテーブルのテーブル名を指定します。複数のテーブルを結合する場合はまず結合したいテーブルをJOINのあとに指定します。そしてONのあとに結合条件を指定します。結合条件を指定せずに...


  • SQL DISTINCTで重複行を1行のみ表示する方法

    SELECTのあとにDISTINCTをつけると、同一のレコードを1行のみ取得するようになります。上記例ではid=1と4のレコードの取得結果は同じになるので、本来は2行出力されるはずのレコードが、DIS...


  • PostgreSQL 現在のシステム日付・時刻を取得する

    OracleのSYSDATEのように、PostgreSQLで現在のシステム日付や時刻を取得する方法を紹介します。YYYY/MM/DDやYYYY-MM-DDのように、date型を取得するにはCURREN...


  • SQL FROM句での副問合わせの基本的な考え方と注意点

    FROM句で副問合わせを行うと、SELECTした結果を1つのテーブルとして、他のテーブルと結合できます。SELECTした結果をVIEWとして予め定義しておくことができますが、FROM句で副問合わせでは...


  • PostgreSQL 日付の加算・減算を行う

    PostgreSQLで日付の加算・減算を行う方法を紹介します。日付型のカラムに対しては、数字の計算と同じ要領で行うことができます。YYYYMMDD形式などの文字列型のカラムの場合は、日付型に変換してか...


  • PostgreSQL ISNULLやNVLのようにNULLを判定する方法

    PostgreSQLでSQL ServerのISNULLやOracleのNVLのように、NULLの場合に別の値を取得するにはCOALESCEを使用します。使い方はISNULLやNVLと同じように、第一...


  • PL/SQL 変数を宣言して値を設定する

    PL/SQLで変数を宣言して値を設定する方法を紹介します。ASとBEGINの間で変数を宣言します。宣言と同時に値をセット(初期化)も可能です。変数への代入は=ではなく:=で行います。


  • PL/SQL TRUNCATE TABLEをストアドプロシージャに定義する

    PL/SQLでTRUNCATE処理をストアドプロシージャに定義する方法を紹介します。そのまま定義すると「記号TABLEが見つかりました。」のコンパイルエラーになってしまうので、動的SQLで定義します。