VB.NET-TIPS などプログラミングについて

データのグループ化（集計処理）を行う時には GROUP BY句 を使うことは、以下の記事で説明しました。
⇒MySQL データのグループ化（集計処理）について(GROUP BY句、WITH ROLLUP)

今回は、グループ化された結果データに対して条件付けの方法について説明します。 この条件を付けのために HAVING句 を使いますがその例と、さらに WHERE句 と同時に使った時の注意点について記します。

以下の内容で、順を追って説明します。

• HAVING句の書き方ついて
• HAVING句にグループ化のカラム名を使う例について
• HAVING句に集計関数を使う例について
• SQL文の中に WHERE句とHAVING句を共に使った場合について

■HAVING句の書き方ついて

HAVING句の書き方は以下の様な構文で指定します。

SELECT カラム名1 [, カラム名2, ...] ,[集計関数...]  FROM テーブル名...
GROUP BY カラム名1 [, カラム名2, ...]
HAVING 条件式

GROUP BY の後ろに指定されたカラム名の値が同じデータのグループ化を行います。 複数のカラム名が指定された場合は、それぞれのカラム名の値が同じものでグループ化を行います。

HAVING句は は GROUP BY の後ろに「HAVING」に続いて条件式を記述します。 条件式はグループ化で使用したカラムに対しする条件や、集計関数を使った条件式となります。

テストデータとして「商品情報」と「売上データ」のテーブルを使い、テーブルの連結を行いながら、 グループ化について説明したいと思います。
これについては、最初に触れましたが、以下の記事のテーブルを使用します。
⇒MySQL データのグループ化（集計処理）について(GROUP BY句、WITH ROLLUP)

■HAVING句にグループ化のカラム名を使う例について

先ずは HAVING句 を使わずに売上データに対してグループ化を行う為に GROUP BY に1個のカラムとして「商品分類」を指定し、 「商品分類」ごとの商品の売上数と売上金額の合計数を表示する SQL を示します。
「商品分類」は「商品マスタ」に持っているため、「売上データ」と「商品マスタ」を FROM句 で INNER JOIN で結合します。

SELECT 
    tm_prod.class AS '商品分類',
    SUM(tt_sales.volume) AS '数量',
    SUM(tt_sales.amount) AS '金額'
FROM tt_sales
    INNER JOIN tm_prod
    ON  tm_prod.id = tt_sales.id_prd
    AND tm_prod.del = 0
GROUP BY tm_prod.class
ORDER BY tm_prod.class;

この SQL の結果は以下の通りです。

+----------+------+-------+
| 商品分類 | 数量 | 金額  |
+----------+------+-------+
| cre      |    9 | 15000 |
| era      |   21 |  3650 |
| kpen     |   13 |  1620 |
| pen      |   58 |  4900 |
+----------+------+-------+
4 rows in set (0.00 sec)

それでは「商品分類」が「pen」の文字列を含むデータを抽出するため HAVING句 を以下の様に追加します。

SELECT 
    tm_prod.class AS '商品分類',
    SUM(tt_sales.volume) AS '数量',
    SUM(tt_sales.amount) AS '金額'
FROM tt_sales
    INNER JOIN tm_prod
    ON  tm_prod.id = tt_sales.id_prd
    AND tm_prod.del = 0
GROUP BY tm_prod.class
HAVING tm_prod.class LIKE '%pen%'
ORDER BY tm_prod.class;

この SQL の結果は以下の通りです。

+----------+------+------+
| 商品分類 | 数量 | 金額 |
+----------+------+------+
| kpen     |   13 | 1620 |
| pen      |   58 | 4900 |
+----------+------+------+
2 rows in set (0.01 sec)

確かに「商品分類」に「pen」の文字列を含むデータのみが表示されました。

■HAVING句に集計関数を使う例について

今度は上記の集計 SQL に「金額」が4000円以上のデータを抽出するため HAVING句 を以下の様に追加します。

SELECT 
    tm_prod.class AS '商品分類',
    SUM(tt_sales.volume) AS '数量',
    SUM(tt_sales.amount) AS '金額'
FROM tt_sales
    INNER JOIN tm_prod
    ON  tm_prod.id = tt_sales.id_prd
    AND tm_prod.del = 0
GROUP BY tm_prod.class
HAVING SUM(tt_sales.amount) >= 4000
ORDER BY tm_prod.class;

この SQL の結果は以下の通りです。

+----------+------+-------+
| 商品分類 | 数量 | 金額  |
+----------+------+-------+
| cre      |    9 | 15000 |
| pen      |   58 |  4900 |
+----------+------+-------+
2 rows in set (0.02 sec)

確かに金額が4000円以上のデータのみが表示されました。

■SQL文の中に WHERE句とHAVING句を共に使った場合について

集計 SQL では WHERE句 と HAVING句 を共に使った場合に、 先ず WHERE句 で対象となるデータを絞り込んで集計処理を行います。 その後で HAVING句 の条件を加味して最終的なデータが取得されます。

今回の例を示す前に「売上データ」の一覧を再度表示します。

MariaDB [test]> select * from tt_sales;
+----+--------+------------+--------+-------+--------+------+
| no | id_prd | salesdate  | volume | price | amount | del  |
+----+--------+------------+--------+-------+--------+------+
|  1 |      1 | 2024-01-04 |     10 |    80 |    800 |    0 |
|  2 |      4 | 2024-01-04 |      1 |  1000 |   1000 |    0 |
|  3 |      6 | 2024-01-04 |      2 |   120 |    240 |    0 |
|  4 |      7 | 2024-01-05 |      3 |   130 |    390 |    0 |
|  5 |      1 | 2024-01-07 |     15 |    80 |   1200 |    0 |
|  6 |      2 | 2024-01-07 |     10 |   100 |   1000 |    0 |
|  7 |      5 | 2024-01-07 |      2 |  2000 |   4000 |    0 |
|  8 |      1 | 2024-01-05 |     20 |    80 |   1600 |    0 |
|  9 |      3 | 2024-01-05 |      1 |   100 |    100 |    0 |
| 10 |      4 | 2024-01-10 |      2 |  1000 |   2000 |    0 |
| 11 |      2 | 2024-01-15 |      2 |   100 |    200 |    0 |
| 12 |      6 | 2024-01-05 |      5 |   120 |    600 |    0 |
| 13 |      7 | 2024-01-14 |      3 |   130 |    390 |    0 |
| 14 |      8 | 2024-01-20 |      1 |   150 |    150 |    0 |
| 15 |      5 | 2024-01-20 |      4 |  2000 |   8000 |    0 |
| 16 |      8 | 2024-01-22 |     10 |   150 |   1500 |    0 |
| 17 |      9 | 2024-01-22 |     10 |   200 |   2000 |    0 |
+----+--------+------------+--------+-------+--------+------+
17 rows in set (0.00 sec)

WHERE句 での条件付けとして「売上日(salesdate)」の範囲を指定し、 さらに HAVING句 で「金額」の範囲を条件づけます。
「売上日(salesdate)」が「2024-01-05 ～ 2024-01-20」の範囲で、「金額」は4000円以上で、 以下の様な SQL となります。

SELECT 
    tm_prod.class AS '商品分類',
    SUM(tt_sales.volume) AS '数量',
    SUM(tt_sales.amount) AS '金額'
FROM tt_sales
    INNER JOIN tm_prod
    ON  tm_prod.id = tt_sales.id_prd
    AND tm_prod.del = 0
WHERE tt_sales.salesdate BETWEEN '2024-01-05' AND '2024-01-20'
GROUP BY tm_prod.class
HAVING SUM(tt_sales.amount) >= 4000
ORDER BY tm_prod.class;

この SQL の結果は以下の通りです。

+----------+------+-------+
| 商品分類 | 数量 | 金額  |
+----------+------+-------+
| cre      |    8 | 14000 |
| pen      |   48 |  4100 |
+----------+------+-------+
2 rows in set (0.03 sec)