実現したいこと
Mysqlのデーターをより簡潔に集計して出力したい
CREATE TABLE IF NOT EXISTS `orders` ( `dat` date NOT NULL DEFAULT '0000-00-00', `user_no` int(11) DEFAULT NULL, `honsu` int(11) NOT NULL DEFAULT '1', `type` int(11) DEFAULT NULL, `price` int(11) DEFAULT NULL ) ENGINE=MyISAM AUTO_INCREMENT=216569 DEFAULT CHARSET=utf8; INSERT INTO `orders` (`dat`, `user_no`, `honsu`, `type`, `price`) VALUES ('2023-02-14', 3314, 3, 1, 3000), ('2023-02-14', 3314, 3, 1, 2000), ('2023-02-15', 3556, 1, 2, 2000), ('2023-02-16', 3540, 1, 2, 1000), ('2023-02-17', 3141, 1, 1, 8000), ('2023-02-18', 3561, 2, 2, 5000), ('2023-02-18', 3573, 1, 2, 1000), ('2023-02-18', 3314, 2, 1, 3000), ('2023-02-19', 3314, 1, 1, 4000)
上記のようなテーブルがあった際に
下記の集計を出したい
①user_noごとの2月のprice合計
②user_noごとの2月のtype=1のprice合計
③user_noごとの2月のtype=2のprice合計
④user_noごとの2月のhonsuの合計
ここがネックなのですが
⑤user_noごとで、1日あたりのhonsuの合計が2を超えている場合は+2000
この月間合計を出したい
例) user_no=3314 の場合
2023-02-14のhonsuは3本と3本で計6本なので+2000
2023-02-18のhonsuは2本なので+2000
2023-02-19のhonsuは1本なので0
2023-02の合計は4000
発生している問題・エラーメッセージ
SELECT sum(price) AS total, sum( CASE WHEN type = 1 THEN price ELSE 0 END ) AS type1_total, sum( CASE WHEN type = 2 THEN price ELSE 0 END ) AS type2_total, sum(honsu) AS honsu_total, FROM orders WHERE DATE_FORMAT(dat,'%m') = '02' GROUP BY user_no
こちらで①〜④のデータは出力できたのですが
⑤に関して下記のように試しました。
SELECT sum(price) AS total, sum( CASE WHEN type = 1 THEN price ELSE 0 END ) AS type1_total, sum( CASE WHEN type = 2 THEN price ELSE 0 END ) AS type2_total, sum(honsu) AS honsu_total, CASE WHEN sum(honsu) >= 2 THEN 2000 ELSE 0 END AS honsu_plus, FROM orders WHERE DATE_FORMAT(dat,'%m') = '02' GROUP BY user_no
が、当然結果はGROUP BY user_noとしていますのでuser_noのhonsuトータルが2以上の場合は2000となるので、2000のみ出力される
次にGROUP BY datでまとめたサブクエリをねじ込んだ場合。エラーになりました。
SELECT sum(price) AS total, sum( CASE WHEN type = 1 THEN price ELSE 0 END ) AS type1_total, sum( CASE WHEN type = 2 THEN price ELSE 0 END ) AS type2_total, sum(honsu) AS honsu_total, sum( SELECT sum(CASE WHEN sum(honsu) >= 2 THEN 2000 ELSE 0 END) AS h_plus FROM orders WHERE DATE_FORMAT(dat,'%m') = '02' GROUP BY dat ) AS honsu_plus, FROM orders WHERE DATE_FORMAT(dat,'%m') = '02' GROUP BY user_no
試したこと
まずは単純に⑤だけを試すために下記を試すもエラー
SELECT SUM(CASE WHEN sum(honsu) >= 2000 THEN 1 ELSE 0 END ) AS honsu_plus FROM orders WHERE DATE_FORMAT(dat,'%m') = '02' AND user_no =3314 GROUP BY dat
下記を試した場合
SELECT dat, CASE WHEN sum(honsu) >= 2000 THEN 2000 ELSE 0 END AS honsu_plus FROM orders WHERE DATE_FORMAT(dat,'%m') = '02' AND user_no =3314 GROUP BY dat
下記のような結果は得られる。これを合計したいだけなんだが・・・
dat honsu_plus 2023-02-14 2000 2023-02-18 2000 2023-02-19 0
どのようにすれば希望の結果が得られますでしょうか
回答3件
あなたの回答
tips
プレビュー