MySQLで日ごとの売上の合計を地域別で表示させたい

Question

やりたいこと：
MySQLで日ごとの売上の合計を地域別で表示させたい

起きている問題：
地域ごとの売り上げが全て同じ値になる

考えられる原因：
おそらくサブクエリを使用して地域ごとの表示にしている部分で、SUMを使用したことで日ごとの合計が取れていないと思うのですが、解決方法を探しても見つからない状況です。

【SQL】

select
DATE_FORMAT(sp.pay_date, "%Y%/%m%/%d") as "日付",
(select SUM(price) from student_payments where area_id = 1) as "東京",
(select SUM(price) from student_payments where area_id = 2) as "山形",
(select SUM(price) from student_payments where area_id = 3) as "大阪",
(select SUM(price) from student_payments where area_id = 4) as "京都",
ROUND((select SUM(price) from student_payments where area_id = 1) + (select SUM(price) from student_payments where area_id = 2) + (select SUM(price) from student_payments where area_id = 3) + (select SUM(price) from student_payments where area_id = 4)) as "全支社合計"
from
student_payments sp
join
areas a
on
sp.area_id = a.id
group by DATE_FORMAT(pay_date, "%Y%m%d")
order by sp.pay_date desc

出力結果

【テーブル】
student_payments

areas

不足情報がありましたらご指摘いただければと思います。
よろしくお願いします。

Comments

[退会済みユーザー]

サブクエリーで日付、地域、売上を求めるものを作って、日付ごとの合計を求めるクエリーにしたらいいんじゃない？

[rat_mouse]

(select SUM(price) from student_payments where area_id = 1)
↑
この部分のwhere句に日付、地域、売上を絞る記述をするということでしょうか？
サブクエリ内で日付で絞る方法がわからず、昨日から悩んでおり、アドバイスをいただけないでしょうか。

[yambejp]

サンプルは画像ではなくテキストでのせてください
可能であればcreate table+insert形式で提示お願いします

Answer 1

SUMを使用したことで日ごとの合計が取れていないと思うのですが

いいえ。
サブクエリーとしている時点で、SUM()[集計関数]は関係ありませんので、サブクエリーで日付に関係なく集計された値が返却されています。
※group byを強制するモードではエラーになるパターンです。

select  pay_date as 日付
      , SUM(price *(area_id = 1)) as 東京
      , SUM(price *(area_id = 2)) as 山形
      , SUM(price *(area_id = 3)) as 大阪
      , SUM(price *(area_id = 4)) as 京都
      , ROUND(SUM(price)) as 全支社合計
from  student_payments sp
      inner join areas a
      on    sp.area_id = a.id
group by pay_date
order by pay_date desc

Comments

[rat_mouse]

ありがとうございました！
サブクエリを使用することに引っ張られていたのですが、シンプルな記述でも集計できるのですね！

Answer 2

select date(sp.pay_date) as "dt", sp.area_id, sum(sp.price) as "sales" 
from student_payments as sp 
group by date(sp.pay_date), sp.area_id;

このカタチで、日付、エリア、売上計が求まると思うんだけど、

select s.dt, 
  sum(case when s.area_id = 1 then s.sales else 0 end) as "sales_Tokyo", 
  sum(case when s.area_id = 2 then s.sales else 0 end) as "sales_Yamagata", 
  ...,
  sum(s.sales) as "sales_all"
from (
  select date(sp.pay_date) as "dt", sp.area_id, sum(sp.price) as "sales" 
  from student_payments as sp 
  group by date(sp.pay_date), sp.area_id
) as s
group by s.dt;

こうなるとエリアごとの合計と全ての合計が求まりそう。
だけど、エリアっていっぱいあるよね。
「s.area_id = いくつ」のバリエーションを完備すればほしいのは求まると思うけど、
もうちょっと楽に書く方法があるはずなので、もうちょっと考えます。。。

Answer 3

pay_date,price,area_idだけで単純化した方がいいですね

create table tbl
(id int primary key,pay_date date,price int,area_id int);

insert into tbl values
(1,'2021-07-20',1000,1),
(2,'2021-07-20',1100,2),
(3,'2021-07-21',1400,1),
(4,'2021-07-21',2000,1),
(5,'2021-07-21',2500,2),
(6,'2021-07-22',1700,1),
(7,'2021-07-22',1300,3);

select pay_date
,sum(price*(area_id=1)) as tokyo
,sum(price*(area_id=2)) as yamagata
,sum(price*(area_id=3)) as osaka
,sum(price*(area_id=4)) as kyoto
,sum(price) as `all`
from tbl
group by pay_date

※横の計がぬけてたので追記しました

Comments

[yambejp]

ようはピボット処理ですよね？
MySQLの場合例示のような書き方になります