【MySQL】別テーブルの項目ごとに件数を求める方法

こんにちは。ぽこです！

今回は、マスターテーブルの項目ごとの件数を横持で取得する方法について書きます。

やりたいこと
やりかた

やりたいこと

今回やりたいのは、次のほうなことになります。

次のようなフルーツマスタと売り上げテーブルを想定します。

ID	名前
1	いちご
2	バナナ
3	メロン
4	みかん

フルーツマスタ

売り上げ日	商品	金額
20230101	1	300
20230101	3	1000
20230101	2	100
20230101	1	300
20230101	1	300
20230102	2	100
20230102	1	300
20230102	4	300
20230102	2	100

売り上げテーブル

この時、日付ごとに商品がいくつ売れたのかを次のように横並びに出力したいです。

売り上げ日	いちご	バナナ	メロン	みかん	合計
20230101	3	1	1	0	5
20230102	1	2	0	1	4

やりかた

今回は、PHPを使ってSQL文を作り、それを実行しました。

まず、売り上げ日ごとのいちごの売り上げ個数は次のように取得できます。

SELECT COUNT(商品 = 1 OR NULL) FROM 売り上げテーブル GROUP BY 売り上げ日

1	SELECT COUNT(商品 = 1 OR NULL) FROM 売り上げテーブル GROUP BY 売り上げ日

これを、フルーツマスタに登録されているフルーツの分だけ横に並べる必要があります。

そのために、フルーツマスタのデータを$fruitsに入れて、次のようにします。

$sql_count = &quot;&quot;;
foreach ($fruits as $key=&gt;$val){
   $sql_count .= &quot;COUNT(商品 = &quot;.$val[&#39;ID&#39;].&quot; OR NULL) &quot;.$val[&#39;名前&#39;].&quot;, &quot;;
}

$sql_count = "";

foreach ($fruits as $key=>$val){

$sql_count .= "COUNT(商品 = ".$val['ID']." OR NULL) ".$val['名前'].", ";

}

ここで、SELECTで使うCOUNT文を作ります。カラム名にフルーツの名前が来るように設定しています。

続いて、先ほど作成したものを使ってSQL全体を作成します。

$sql = SELECT 売り上げ日, &quot;.$sql_count.&quot;COUNT(*) FROM 売り上げテーブル GROUP BY 売り上げ日

1	$sql = SELECT 売り上げ日, ".$sql_count."COUNT(*) FROM 売り上げテーブル GROUP BY 売り上げ日