SQL プログラミング

【GROUP BYの基本】グループ単位で集計するには

スポンサーリンク

SQLec03
頼れる人
あるカラムの合計や件数を、グループ単位で取得したいときにはどうすればいいのか？
集計関数を使ったやり方を教えるよ
ここだけ読めばグループ単位での集計がわかるようになるよ
ぴよちゃん
もうすでにGROUP BYを使ったグループ集計の方法を知ってるよ！という人は、ここは読まなくていいですよ。
こけさん

集計関数を使って、合計や件数、最大値などを取得しよう

合計値を取得しよう（SUM）

金額や数量の合計を求めるには、SUM関数を使います。（SUMMARYの略です）
合計を求めたいカラムをSUM関数で指定します。

// 商品テーブルの金額 [amount] を合計を求める
SELECT SUM(amount)
FROM product;

// SUM(amount)　⇒　29000

では、金額と数量の２つのカラムの合計を求めたいときには、どうしたらいいのでしょうか？
合計を求めたいカラムをSUM関数で指定しますが、カンマで区切って指定します。
しかし、カンマ区切りをするのは、SUM関数の中ではなく、SUM関数をつなげて指定します。

// 商品テーブルの金額 [amount] と数量 [qty] を合計を求める
SELECT SUM(amount)
      ,SUM(qty)
FROM product;

// SUM(amount), SUM(qty)　⇒　29000, 70
// 商品テーブルの金額 [amount] と数量 [qty] を合計を求める
SELECT SUM(amount, qty)
FROM product;

// これはSQLエラー

テーブルに１件もレコードがなかったときには、NULL値が返ってきます。

件数を取得しよう（COUNT）

レコード数を求めるには、COUNT関数を使います。
件数を求めたいカラムをCOUNT関数で指定します。

// 商品テーブルの数量 [qty] の件数を求める
SELECT COUNT(qty)
FROM product;

// COUNT(qty)　⇒　3

値が入っていないカラム（NULL値）が含まれているとき、COUNT（カラム名）で件数を取得すると、値が入っていないカラムのレコードはカウントされません。
全ての件数を求める場合には、COUNT関数のカッコ内にアスタリスク（* ）を指定します。

// 商品テーブルの数量 [qty] の件数を求める
SELECT COUNT(qty)
FROM product;

// COUNT(qty)　⇒　2
// 商品テーブルの件数を求める
SELECT COUNT(*)
FROM product;

// COUNT(*)　⇒　3

テーブルに１件もレコードがなかったときには、ゼロが返ってきます。

最大値を取得しよう（MAX）

金額の中から最大値を求めるには、MAX関数を使います。（MAXIMUMの略です）
最大値を求めたいカラムをMAX関数で指定します。

// 商品テーブルの金額 [amount] の最大値を求める
SELECT MAX(amount)
FROM product;

// MAX(amount)　⇒　11000

金額と数量の最大値を求めたいときには、MAX関数をカンマ区切りでつなげて指定します。

// 商品テーブルの金額 [amount] と数量 [qty] の最大値を求める
SELECT MAX(amount)
      ,MAX(qty)
FROM product;

// MAX(amount), MAX(qty)　⇒　11000, 30

値が入っていないカラム（NULL値）が含まれているとき、MAX（カラム名）で最大値を取得すると、NULLが返ってきます。

// 商品テーブルの数量 [qty] の最大値を求める
SELECT MAX(qty)
FROM product;

// MAX(qty)　⇒　NULL（30ではない）

テーブルに１件もレコードがなかったときには、NULL値が返ってきます。

最小値を取得しよう（MIN）

金額の中から最小値を求めるには、MIN関数を使います。（MINIMUMの略です）
最小値を求めたいカラムをMIN関数で指定します。

// 商品テーブルの金額 [amount] の最小値を求める
SELECT MIN(amount)
FROM product;

// MIN(amount)　⇒　8000

金額と数量の最小値を求めたいときには、MIN関数をカンマ区切りでつなげて指定します。

// 商品テーブルの金額 [amount] と数量 [qty] の最小値を求める
SELECT MIN(amount)
      ,MIN(qty)
FROM product;

// MIN(amount), MIN(qty)　⇒　8000, 15

値が入っていないカラム（NULL値）が含まれているとき、MIN（カラム名）で最小値を取得すると、NULLが返ってきます。

// 商品テーブルの数量 [qty] の最小値を求める
SELECT MIN(qty)
FROM product;

// MIN(qty)　⇒　NULL（25ではない）

テーブルに１件もレコードがなかったときには、NULL値が返ってきます。

平均値を取得しよう（AVG）

金額の平均値を求めるには、AVG関数を使います。（AVERAGEの略です）
平均値を求めたいカラムをAVG関数で指定します。

// 商品テーブルの金額 [amount] の平均値を求める
SELECT AVG(amount)
FROM product;

// AVG(amount)　⇒　9666.6…

金額と数量の平均値を求めたいときには、AVG関数をカンマ区切りでつなげて指定します。

// 商品テーブルの金額 [amount] と数量 [qty] の平均値を求める
SELECT AVG(amount)
      ,AVG(qty)
FROM product;

// AVG(amount), AVG(qty)　⇒　9666.6…, 23.3…

値が入っていないカラム（NULL値）が含まれているとき、AVG（カラム名）で平均値を取得すると、値が入ってないカラムを無視して平均値が返ってきます。

// 商品テーブルの数量 [qty] の平均値を求める
SELECT AVG(qty)
FROM product;

// AVG(qty)　⇒　(25 + 30) / 2　⇒　27.5

テーブルに１件もレコードがなかったときには、NULL値が返ってきます。

SQLXec01
【豆知識】NULLと空文字との違いは？NULLを扱いを理解しよう！

続きを見る

データをグループ単位で集計しよう

グループ単位で集計するには（GROUP BY）

注文日ごとの金額合計を求めるときには、GROUP BYを使って、どのカラムでグルーピングするかを指定します。
先に紹介した集計関数であれば、どれでも利用することができます。

// 注文テーブルの注文日 [orderdate] ごとに、金額 [anount] の合計値を求める
SELECT orderdate
      ,SUM(amount)
FROM orders
GROUP BY orderdate;

// 2021-01-10, 28000
// 2021-01-11, 21000

SELECT句に指定できるのは、GROUP BYで指定したカラムと、集計関数のみとなります。
GROUP BYで指定していないカラムをSELECT句に指定するとエラーとなります。

// 注文テーブルの注文日 [orderdate] ごとに、金額 [anount] の合計値を求める
SELECT orderdate
      ,name
      ,SUM(amount)
FROM orders
GROUP BY orderdate;

// これはSQLエラー（nameがGROUP BYで指定されていない）

複数のカラムでグルーピングするには

注文日と商品名ごとの金額合計と数量合計を求めたいときには、グルーピングしたいカラムをカンマ区切りでつなげて指定します。

// 注文テーブルの注文日 [orderdate] と商品名 [name] ごとに、金額 [anount] と数量 [qty] の合計値を求める
SELECT orderdate
      ,name
      ,SUM(amount)
      ,SUM(qty)
FROM orders
GROUP BY orderdate
        ,name;

// 2021-01-10, スカート, 18000, 2
// 2021-01-10, ブラウス, 18000, 2
// 2021-01-11, ジャケット, 11000, 3
// 2021-01-11, スカート, 10000, 1

絞り込んだデータをグループ単位で集計しよう

条件指定してデータを絞り込んだあと、グループ単位で集計することができます。
WHERE句で絞り込みたい条件を指定し、集計する単位でグルーピングします。
この場合には、先に商品名が「スカート」だけを取得し、その中で日付と商品名でのグループ単位で集計が行われます。
条件に一致しないレコードは集計されません。

// 注文テーブルから商品名 [name] がスカートで、注文日 [orderdate] と商品名 [name] ごとに、金額 [anount] と数量 [qty] の合計値を求める
SELECT orderdate
      ,name
      ,SUM(amount)
      ,SUM(qty)
FROM orders
WHERE name = 'スカート'
GROUP BY orderdate
        ,name;

// 2021-01-10, スカート, 18000, 2
// 2021-01-11, スカート, 10000, 1

グループ単位で集計したデータから一部を取得しよう（HAVING）

集計した結果に対してデータを絞り込みたいときには、HAVING句を使います。
HAVING句で指定するときには、集計関数も含めて指定します。ここではGROUP BYで指定したカラムや、その他のカラムを指定することはできません。

// 注文テーブルの注文日 [orderdate] と商品名 [name] ごとに、金額 [anount] と数量 [qty] の合計値を求める
// 数量 [qty] が2以上のみを取得する
SELECT orderdate
      ,name
      ,SUM(amount)
      ,SUM(qty)
FROM orders
GROUP BY orderdate
        ,name
HAVING SUM(qty) >= 2;

// 2021-01-10, スカート, 18000, 2
// 2021-01-10, ブラウス, 18000, 2
// 2021-01-11, ジャケット, 11000, 3

また、これまで出てきたWHERE句に集計関数を指定することもできません。

// 注文テーブルの注文日 [orderdate] と商品名 [name] ごとに、金額 [anount] と数量 [qty] の合計値を求める
// 数量 [qty] が2以上のみを取得する
SELECT orderdate
      ,name
      ,SUM(amount)
      ,SUM(qty)
FROM orders
WHERG SUM(qty) >= 2
GROUP BY orderdate
        ,name;

// これはSQLエラー

まとめ

GROUP BYを使って、グループ単位で集計ができます。
SUM, COUNT, MAX, MIN, AVGといった関数で、集計結果を得ることができます。

集計した結果に対して、条件を指定するときには、HAVING句を使います。
GROUP BYで指定した項目か、集計関数以外はSELECT句では指定できませんので、注意してくださいね。

NULL値が入っているカラムに対して、集計を行うときには、COUNTやAVGではNULL値のレコードは無視されますが、それ以外の集計関数ではNULL値が返ってきます。

▶　一覧に戻る

    スポンサーリンク

    -SQL, プログラミング

    PAGE TOP

    © 2021 ちょこぱいぶろぐ Powered by AFFINGER5