SQL プログラミング

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

スポンサーリンク

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

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

合計値を取得しよう(SUM)

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

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

// SUM(amount) ⇒ 29000

では、金額と数量の2つのカラムの合計を求めたいときには、どうしたらいいのでしょうか?
合計を求めたいカラムを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エラー

テーブルに1件もレコードがなかったときには、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

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

最大値を取得しよう(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ではない)

テーブルに1件もレコードがなかったときには、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ではない)

テーブルに1件もレコードがなかったときには、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

テーブルに1件もレコードがなかったときには、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