SQL GROUP BYによるデータの抽出と集計関数の使い方

SQLのGROUP BY句は、データベース内のテーブルの行をグループ化し、各グループの集計関数(SUM, AVGなど)を使用してデータの要約を行うための重要な機能です。

いくつかの例を用いてGROUP BYと集計関数の使い方について解説いたします。またWITH ROLLUPを使ったサブトータルの算出についても紹介いたします。

1.GROUP BYの使い方

以下にサンプルデータとして注文テーブル(orders)を用意して、GROUP BYを使ってみます。ordersテーブルデータは、orderIDとproductIDの組み合わせで一意になるデータとなります。

注文テーブル (orders):

orderIDをキーとして、orderIDごとのレコードの数を抽出する場合、以下のようなSQLで結果を取得することができます。

SELECT orderID, count(*)
FROM Orders 
GROUP BY orderID;

orderIDをGROUP BYに指定したので、SELECTにもorderIDを指定する必要があります。またcountが集計関数で、count(*)とした場合、レコードの件数をカウントします。

結果は以下のようになります。

orderID=1のレコードは2レコードあったのが、GROUP BYで抽出した後は、1レコードに集約されます。

2.集計とテーブル結合

GROUP BYとテーブルの結合(JOIN)は、よく一緒に使うので、追加のテーブルを用意して、同時に使う例を考えてみます。

新たに商品テーブル(products)を用意して、ordersテーブルとテーブル結合を行い、さらに集計をしてみます。

商品テーブル (products):

ordersとproductsには、それぞれproductIDがあるので、productIDをキーにして、テーブル結合をします。

またGROUP BYを使って、注文ごとの合計金額を求めてみます。

SELECT o.orderID, SUM(p.price) AS totalPrice
FROM orders o
LEFT JOIN products p ON o.productID = p.productID
GROUP BY o.orderID;

LEFT JOINを使用して、productsテーブルをordersテーブルに結合します。またorderIDを使って集計する際、SUM関数を使うことでpriceの合計値を求めることができます。

集計結果は以下のようになります。

テーブル結合までであれば、ordersテーブルの全レコードを取得するので、4レコードが返ってきますが、さらにGROUP BYによりorderIDをキーにして集計を行うので、3レコードとなります。

テーブル結合については、以下の記事でも解説しています。

SQL初心者向けチュートリアル - テーブルの結合：INNER JOINとLEFT JOINの比較

CROSS JOIN、INNER JOIN、LEFT JOINの違いについて、それぞれ具体例を用いて説明します。以前、CRUDについて解説した記事にて、booksとbook_salesテーブルを用いました。本記事はその続きであり、2つのテーブルを例に解説していきます。

masa-engineer-blog.com

2023.10.12

3.GROUP BYとHAVINGを使用した集計クエリ

GROUP BYは、データをグループ化し、各グループの結果を集計するために使用されます。HAVINGは、集計結果に対してフィルタリング条件を適用するために使用されます。

GROUP BYとHAVINGの具体例

例: 注文ごとの合計金額が500以上の注文データを抽出する

SELECT o.orderID, SUM(p.price) AS totalPrice
FROM orders o
LEFT JOIN products p ON o.productID = p.productID
GROUP BY o.orderID
HAVING SUM(p.price) >= 500;

前のSQLにさらにHAVINGを追加したものになります。

これにより、GROUP BYで集計した結果に対して、絞り込みをすることができます。

4.集計関数

COUNT、SUM、AVG、MAX、MINについてそれぞれ簡単に解説いたします。

これらはGROUP BYと組み合わせて使うこともありますが、単体でも使うことができます。

COUNT

レコードの件数を求める関数です。

select count(*) from products;

こちらの結果は、4となります。

select count(productName) from products;

こちらの結果は、3となります。

productID=104のproductNameは、nullとなっていて、nullは値がないことを意味するので、上記のようにnullが含まれるカラムを指定して、countを実行すると、nullを含むレコードはカウントされません。

SUM

合計値を求める関数です。

select SUM(price) from products;

こちらの結果は、2000となります。

GROUP BYを指定してないので、全てのレコードを1件に集約した合計値が算出されます。

AVG

平均値を求める関数です。

select AVG(price) from products;

こちらの結果は、500となります。

全体の合計が2000で4レコードあるので、2000 / 4 = 500となります。

MAX

最大値を求める関数です。

select MAX(price) from products;

こちらの結果は、800となります。

MIN

最小値を求める関数です。

select MIN(price) from products;

こちらの結果は、300となります。

5.WITH ROLLUPを使ったサブトータルの算出

WITH ROLLUPは、SQLのクエリで集計データを階層的に表示するために使用される機能です。特に、GROUP BY句を使用してデータを集計し、それをさらにロールアップして合計やサブトータルを表示する際に役立ちます。以下に、WITH ROLLUPを使った具体的なSQLクエリの例を示します。

この例では、商品カテゴリとジャンル別の商品の合計値を考えます。以下のテーブルを使用します：

商品テーブル (Products):

次に、カテゴリ、ジャンル別の商品価格の合計値をWITH ROLLUPを使用して計算するSQLクエリは以下のようになります。

SELECT category,genre, SUM(price) AS totalPrice
FROM products
GROUP BY category, genre WITH ROLLUP;

• GROUP BY にcategory, genre の2つキーを指定して集計しています
• GROUP BY category, genre WITH ROLLUP は、カテゴリとジャンルを基準にデータをグループ化し、WITH ROLLUPを使用して合計とサブトータルを生成します。
• SUM(Price) は、各商品の価格を合計し、合計金額は “totalPrice” という名前で表示されます。

クエリの結果は以下のようになります：

この結果では、WITH ROLLUPを使用して、カテゴリ、ジャンル別の商品価格の合計と、さらに全体の価格の合計が表示されています。NULLの行は合計を示しており、行に表示されていないカテゴリやジャンル別の合計も計算されています。