条件が一致したとき、合計値を算出する

Question

###前提・実現したいこと
その年の売上を月毎に算出してます。
また、会社、販売場所をグループ化しています。

店舗と店舗免税を区別せずに店舗として、合計値を算出したいです。

ご教授の程、宜しくお願い申し上げます。

###現在の状態

###該当のソースコード

SELECT 
	CASE  
		WHEN (`orderer_id`= 2 AND `point_of_sale_id`=2) THEN 'F  社 - 店舗' 
		WHEN (`orderer_id`= 2 AND `point_of_sale_id`=3) THEN 'F  社 - 店舗免税'
		WHEN (`orderer_id`= 2 AND `point_of_sale_id`=4) THEN 'F  社 - 国内通販' 
		WHEN (`orderer_id`= 2 AND `point_of_sale_id`=5) THEN 'F  社 - 海外通販' 
		WHEN (`orderer_id`= 2)                          THEN 'F  社 - 合計' 
		WHEN (`orderer_id`= 3 AND `point_of_sale_id`=2) THEN 'R  社 - 店舗' 
		WHEN (`orderer_id`= 3 AND `point_of_sale_id`=3) THEN 'R  社 - 店舗免税' 
		WHEN (`orderer_id`= 3 AND `point_of_sale_id`=4) THEN 'R  社 - 国内通販' 
		WHEN (`orderer_id`= 3 AND `point_of_sale_id`=5) THEN 'R  社 - 海外通販' 
		WHEN (`orderer_id`= 3)                          THEN 'R  社 - 合計' 
		WHEN (`orderer_id`= 4 AND `point_of_sale_id`=2) THEN 'C  社 - 店舗' 
		WHEN (`orderer_id`= 4 AND `point_of_sale_id`=3) THEN 'C  社 - 店舗免税' 
		WHEN (`orderer_id`= 4 AND `point_of_sale_id`=4) THEN 'C  社 - 国内通販' 
		WHEN (`orderer_id`= 4 AND `point_of_sale_id`=5) THEN 'C  社 - 海外通販'  
		WHEN (`orderer_id`= 4)                          THEN 'C  社 - 合計' 
	END AS '発注元の会社 - 販売した場所' 
	,sum.`1月`
    ,sum.`2月`
    ,sum.`3月`
	,sum.`4月`
    ,sum.`5月`
    ,sum.`6月`
	,sum.`7月`
    ,sum.`8月`
    ,sum.`9月`
	,sum.`10月`
    ,sum.`11月`
    ,sum.`12月`
FROM (
	SELECT 
		 `orderer_id`
        ,`point_of_sale_id`
		,SUM(
			IF(
				EXTRACT(MONTH from `payment_confirm`) = '01'
                , CASE 
					WHEN (`orderer_id`= 2 OR `orderer_id`= 3 OR `orderer_id`= 4) AND (`point_of_sale_id`=2 OR `point_of_sale_id`=4) THEN `usually_grand_total` + `grand_consumption_tax_total` 
                    WHEN (`orderer_id`= 2 OR `orderer_id`= 3 OR `orderer_id`= 4) AND (`point_of_sale_id`=3 OR `point_of_sale_id`=5) THEN `custom_grand_total` 
				END
			, 0)
		) AS '1月' 
		,SUM(
			IF(
				EXTRACT(MONTH from `payment_confirm`) = '02'
                , CASE 
					WHEN (`orderer_id`= 2 OR `orderer_id`= 3 OR `orderer_id`= 4) AND (`point_of_sale_id`=2 OR `point_of_sale_id`=4) THEN `usually_grand_total` + `grand_consumption_tax_total` 
                    WHEN (`orderer_id`= 2 OR `orderer_id`= 3 OR `orderer_id`= 4) AND (`point_of_sale_id`=3 OR `point_of_sale_id`=5) THEN `custom_grand_total` 
				END
			, 0)
		) AS '2月' 
		,SUM(
			IF(
				EXTRACT(MONTH from `payment_confirm`) = '03'
                , CASE 
					WHEN (`orderer_id`= 2 OR `orderer_id`= 3 OR `orderer_id`= 4) AND (`point_of_sale_id`=2 OR `point_of_sale_id`=4) THEN `usually_grand_total` + `grand_consumption_tax_total` 
                    WHEN (`orderer_id`= 2 OR `orderer_id`= 3 OR `orderer_id`= 4) AND (`point_of_sale_id`=3 OR `point_of_sale_id`=5) THEN `custom_grand_total` 
				END
			, 0)
		) AS '3月' 
		,SUM(
			IF(
				EXTRACT(MONTH from `payment_confirm`) = '04'
                , CASE 
					WHEN (`orderer_id`= 2 OR `orderer_id`= 3 OR `orderer_id`= 4) AND (`point_of_sale_id`=2 OR `point_of_sale_id`=4) THEN `usually_grand_total` + `grand_consumption_tax_total` 
                    WHEN (`orderer_id`= 2 OR `orderer_id`= 3 OR `orderer_id`= 4) AND (`point_of_sale_id`=3 OR `point_of_sale_id`=5) THEN `custom_grand_total` 
				END
			, 0)
		) AS '4月' 
		,SUM(
			IF(
				EXTRACT(MONTH from `payment_confirm`) = '05'
                , CASE 
					WHEN (`orderer_id`= 2 OR `orderer_id`= 3 OR `orderer_id`= 4) AND (`point_of_sale_id`=2 OR `point_of_sale_id`=4) THEN `usually_grand_total` + `grand_consumption_tax_total` 
                    WHEN (`orderer_id`= 2 OR `orderer_id`= 3 OR `orderer_id`= 4) AND (`point_of_sale_id`=3 OR `point_of_sale_id`=5) THEN `custom_grand_total` 
				END
			, 0)
		) AS '5月' 
		,SUM(
			IF(
				EXTRACT(MONTH from `payment_confirm`) = '06'
                , CASE
					WHEN (`orderer_id`= 2 OR `orderer_id`= 3 OR `orderer_id`= 4) AND (`point_of_sale_id`=2 OR `point_of_sale_id`=4) THEN `usually_grand_total` + `grand_consumption_tax_total` 
                    WHEN (`orderer_id`= 2 OR `orderer_id`= 3 OR `orderer_id`= 4) AND (`point_of_sale_id`=3 OR `point_of_sale_id`=5) THEN `custom_grand_total` 
				END
			, 0)
		) AS '6月' 
		,SUM(
			IF(
				EXTRACT(MONTH from `payment_confirm`) = '07'
                , CASE 
					WHEN (`orderer_id`= 2 OR `orderer_id`= 3 OR `orderer_id`= 4) AND (`point_of_sale_id`=2 OR `point_of_sale_id`=4) THEN `usually_grand_total` + `grand_consumption_tax_total` 
                    WHEN (`orderer_id`= 2 OR `orderer_id`= 3 OR `orderer_id`= 4) AND (`point_of_sale_id`=3 OR `point_of_sale_id`=5) THEN `custom_grand_total` 
				END
			, 0)
		) AS '7月' 
		,SUM(
			IF(
				EXTRACT(MONTH from `payment_confirm`) = '08'
                , CASE 
					WHEN (`orderer_id`= 2 OR `orderer_id`= 3 OR `orderer_id`= 4) AND (`point_of_sale_id`=2 OR `point_of_sale_id`=4) THEN `usually_grand_total` + `grand_consumption_tax_total` 
                    WHEN (`orderer_id`= 2 OR `orderer_id`= 3 OR `orderer_id`= 4) AND (`point_of_sale_id`=3 OR `point_of_sale_id`=5) THEN `custom_grand_total` 
				END
			, 0)
		) AS '8月' 
		,SUM(
			IF(
				EXTRACT(MONTH from `payment_confirm`) = '09'
                , CASE 
					WHEN (`orderer_id`= 2 OR `orderer_id`= 3 OR `orderer_id`= 4) AND (`point_of_sale_id`=2 OR `point_of_sale_id`=4) THEN `usually_grand_total` + `grand_consumption_tax_total` 
                    WHEN (`orderer_id`= 2 OR `orderer_id`= 3 OR `orderer_id`= 4) AND (`point_of_sale_id`=3 OR `point_of_sale_id`=5) THEN `custom_grand_total` 
				END
			, 0)
		) AS '9月' 
		,SUM(
			IF(
				EXTRACT(MONTH from `payment_confirm`) = '10'
                , CASE 
					WHEN (`orderer_id`= 2 OR `orderer_id`= 3 OR `orderer_id`= 4) AND (`point_of_sale_id`=2 OR `point_of_sale_id`=4) THEN `usually_grand_total` + `grand_consumption_tax_total` 
                    WHEN (`orderer_id`= 2 OR `orderer_id`= 3 OR `orderer_id`= 4) AND (`point_of_sale_id`=3 OR `point_of_sale_id`=5) THEN `custom_grand_total` 
				END
			, 0)
		) AS '10月' 
		,SUM(
			IF(
				EXTRACT(MONTH from `payment_confirm`) = '11'
                , CASE 
					WHEN (`orderer_id`= 2 OR `orderer_id`= 3 OR `orderer_id`= 4) AND (`point_of_sale_id`=2 OR `point_of_sale_id`=4) THEN `usually_grand_total` + `grand_consumption_tax_total` 
                    WHEN (`orderer_id`= 2 OR `orderer_id`= 3 OR `orderer_id`= 4) AND (`point_of_sale_id`=3 OR `point_of_sale_id`=5) THEN `custom_grand_total` 
				END
			, 0)
		) AS '11月' 
		,SUM(
			IF(
				EXTRACT(MONTH from `payment_confirm`) = '12'
                , CASE 
					WHEN (`orderer_id`= 2 OR `orderer_id`= 3 OR `orderer_id`= 4) AND (`point_of_sale_id`=2 OR `point_of_sale_id`=4) THEN `usually_grand_total` + `grand_consumption_tax_total` 
                    WHEN (`orderer_id`= 2 OR `orderer_id`= 3 OR `orderer_id`= 4) AND (`point_of_sale_id`=3 OR `point_of_sale_id`=5) THEN `custom_grand_total` 
				END
			, 0)
		) AS '12月' 
	FROM 
		vw_sales_budget 
    WHERE 
		EXTRACT(YEAR from `payment_confirm`) = '2017' 
	AND 
		`orderer_id` NOT IN(1) 
	AND 
		`point_of_sale_id` NOT IN(1) 
	GROUP BY 
		 orderer_id
        ,point_of_sale_id
	WITH ROLLUP 
) AS sum; 

ビュー

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `hironobu`@`localhost` 
    SQL SECURITY DEFINER
VIEW `vw_sales_budget` AS
    SELECT 
        `ps`.`process_status_id` AS `process_status_id`,
        `so`.`orderer_id` AS `orderer_id`,
        `pos`.`point_of_sale_id` AS `point_of_sale_id`,
        `so`.`usually_grand_total` AS `usually_grand_total`,
        `so`.`custom_grand_total` AS `custom_grand_total`,
        `so`.`grand_consumption_tax_total` AS `grand_consumption_tax_total`,
        `so`.`payment_confirm` AS `payment_confirm`
    FROM
        (((`sales_order` `so`
        JOIN `processes_status` `ps` ON ((`so`.`processing_status_id` = `ps`.`process_status_id`)))
        JOIN `companys` `cp` ON ((`so`.`orderer_id` = `cp`.`company_id`)))
        JOIN `point_of_sales` `pos` ON ((`so`.`point_of_sale_id` = `pos`.`point_of_sale_id`)))
    WHERE
        (`so`.`processing_status_id` <> 8)

###補足情報(言語/FW/ツール等のバージョンなど)
MySQL 5.7

Answer 1

全体的に無駄が多いように見えます

GROUP BY orderer_id,point_of_sale_id
で集計すればよいのでは？

sample

まずデータの持ち方は、会社id、売上属性、計上日、数量の4つが必要です
それを踏まえてサンプルデータを以下とします

create table tbl(kaisha int,zokusei int,hiduke date,suuchi int);
insert into tbl values
(1,1,'2017-06-01',100),
(1,1,'2017-06-01',200),
(1,1,'2017-06-02',300),
(1,2,'2017-06-01',150),
(1,2,'2017-06-01',120),
(1,2,'2017-06-01',300),
(1,3,'2017-06-01',200),
(1,3,'2017-06-01',100),
(1,3,'2017-06-01',100),
(1,3,'2017-06-01',300),
(1,3,'2017-07-01',1000),
(2,1,'2017-06-01',100),
(2,1,'2017-06-02',300),
(2,2,'2017-06-01',100),
(2,3,'2017-06-01',100);

集計します

select kaisha,zokusei
,sum((date_format(hiduke,'%m')='05')*suuchi) as m5
,sum((date_format(hiduke,'%m')='06')*suuchi) as m6
,sum((date_format(hiduke,'%m')='07')*suuchi) as m7
from tbl
where date_format(hiduke,'%Y')='2017'
group by kaisha,zokusei

上記サンプルの2と3をまとめて別のコード=99として集計します

select kaisha,case when zokusei in (2,3) then 99 else zokusei end as zokusei2
,sum((date_format(hiduke,'%m')='05')*suuchi) as m5
,sum((date_format(hiduke,'%m')='06')*suuchi) as m6
,sum((date_format(hiduke,'%m')='07')*suuchi) as m7
from tbl
where date_format(hiduke,'%Y')='2017'
group by kaisha,zokusei2

ついでに

会社名と属性を別テーブルで用意してやるとこう

create table t_kaisha(kid int unique key,kname varchar(20));
insert into t_kaisha values(1,'F社'),(2,'R社'),(999,'全社');
create table t_zokusei(zid int unique key,zname varchar(20));
insert into t_zokusei values(1,'店舗'),(2,'免税'),(3,'通販'),(99,'その他'),(999,'合計');

リレーションしてこう(小計欄集計をつけときました)

select concat(kname,' - ',zname) as kz
,coalesce(kaisha,999) as kaisha
,coalesce(zokusei2,999) as zokusei2
,t1.m5
,t1.m6
,t1.m7
from (
select kaisha,case when zokusei in (2,3) then 99 else zokusei end as zokusei2
,sum((date_format(hiduke,'%m')='05')*suuchi) as m5
,sum((date_format(hiduke,'%m')='06')*suuchi) as m6
,sum((date_format(hiduke,'%m')='07')*suuchi) as m7
from tbl,t_zokusei
where date_format(hiduke,'%Y')='2017'
group by kaisha asc ,zokusei2 asc with rollup
) as t1
left join t_kaisha on coalesce(kaisha,999)=kid
left join t_zokusei on coalesce(zokusei2,999)=zid
order by kaisha,zokusei2;

修正版？

create table tbl(kaisha int,zokusei int,hiduke date,suuchi int);
insert into tbl values
(1,1,'2017-06-01',100),
(1,2,'2017-06-01',100),
(1,3,'2017-06-01',100),
(1,4,'2017-06-01',100),
(2,1,'2017-06-01',100),
(2,2,'2017-06-01',100),
(2,3,'2017-06-01',100),
(2,4,'2017-06-01',100),
(3,1,'2017-06-01',100),
(3,2,'2017-06-01',100),
(3,3,'2017-06-01',100),
(3,4,'2017-06-01',100);
create table t_kaisha(kid int unique key,kname varchar(20));
insert into t_kaisha values(1,'F社'),(2,'R社'),(3,'C社'),(999,'合計');
create table t_zokusei(zid int unique key,zname varchar(20));
insert into t_zokusei values(1,'店舗'),(2,'免税'),(3,'国内通販'),(4,'海外通販'),(999,'合計');
select concat(kname,' - ',zname) as kz
,coalesce(kaisha,999) as kaisha
,coalesce(zokusei2,999) as zokusei2
,t1.m6
from (
select kaisha,case when zokusei in (1,2) then 1 else zokusei end as zokusei2
,sum((date_format(hiduke,'%m')='06')*suuchi) as m6
from tbl
where date_format(hiduke,'%Y')='2017'
group by kaisha asc ,zokusei2 asc with rollup
) as t1
left join t_kaisha on coalesce(kaisha,999)=kid
left join t_zokusei on coalesce(zokusei2,999)=zid
order by kaisha,zokusei2;

Comments

[marshmallowy]

すみません。
見当がつかないです。

何か例を見せていただくことは可能でしょうか？

[marshmallowy]

ちょっと、気になるのですが、point_of_sale_idの値が異なる場合でも、異なる値同士をグループ化できるのでしょうか？

[yambejp]

sample追記しました

[marshmallowy]

サンプルを確認いたしました。
ありがとうござます。

また、zokuseiカラムのID、1と2を区別せずに合計値を算出する方法はございますでしょうか？

[yambejp]

そういえばそういう命題でしたね
属性をまとめる方法を追記しておきました

[marshmallowy]

ホンとに綺麗です。
勉強になりました。
いつも、お時間を割いていただき、ありがとうございました。

[yambejp]

ついでに会社名や属性を文字列として渡す場合の処理をいれれば
命題のようなフォーマットになると思います

[marshmallowy]

かしこまりました。

[marshmallowy]

サンプルコードに少し手を加えたのですが、集計が正しく算出されませんでした。
どこに何処で問題が発生しているのかがよくわかりませんでした。
よろしければ、一度、見ていただけませんでしょうか。

[marshmallowy]

[サンプルコード]
insert into t_kaisha values(1,'F社'),(2,'R社'),(999,'全社');
insert into t_zokusei values(1,'店舗'),(2,'免税'),(3,'通販'),(99,'その他'),(999,'合計');
insert into tbl values
(1,1,'2017-06-01',100),
(1,1,'2017-06-01',200),
(1,1,'2017-06-02',300),
(1,2,'2017-06-01',150),
(1,2,'2017-06-01',120),
(1,2,'2017-06-01',300),
(1,3,'2017-06-01',200),
(1,3,'2017-06-01',100),
(1,3,'2017-06-01',100),
(1,3,'2017-06-01',300),
(1,3,'2017-07-01',1000),
(2,1,'2017-06-01',100),
(2,1,'2017-06-02',300),
(2,2,'2017-06-01',100),
(2,3,'2017-06-01',100);

[サンプルコードに手を加えたもの]
insert into t_kaisha values(1,'F社'),(2,'R社'),(3,'C社'),(999,'合計');
insert into t_zokusei values(1,'店舗'),(2,'免税'),(3,'国内通販'),(4,'海外通販'),(999,'合計');
insert into tbl values
(1,1,'2017-06-01',100),
(1,2,'2017-06-01',100),
(1,3,'2017-06-01',100),
(1,4,'2017-06-01',100),
(2,1,'2017-06-01',100),
(2,2,'2017-06-01',100),
(2,3,'2017-06-01',100),
(2,4,'2017-06-01',100),
(3,1,'2017-06-01',100),
(3,2,'2017-06-01',100),
(3,3,'2017-06-01',100),
(3,4,'2017-06-01',100);

[marshmallowy]

[サンプルコード]
select concat(kname,' - ',zname) as kz
,coalesce(kaisha,999) as kaisha
,coalesce(zokusei2,999) as zokusei2
,t1.m5
,t1.m6
,t1.m7
from (
select kaisha,case when zokusei in (2,3) then 99 else zokusei end as zokusei2
,sum((date_format(hiduke,'%m')='05')*suuchi) as m5
,sum((date_format(hiduke,'%m')='06')*suuchi) as m6
,sum((date_format(hiduke,'%m')='07')*suuchi) as m7
from tbl,t_zokusei
where date_format(hiduke,'%Y')='2017'
group by kaisha asc ,zokusei2 asc with rollup
) as t1
left join t_kaisha on coalesce(kaisha,999)=kid
left join t_zokusei on coalesce(zokusei2,999)=zid
order by kaisha,zokusei2;

[サンプルコードに手を加えたもの]
select concat(kname,' - ',zname) as kz
,coalesce(kaisha,999) as kaisha
,coalesce(zokusei2,999) as zokusei2
,t1.m6
from (
select kaisha,case when zokusei in (1,2) then 1 else zokusei end as zokusei2
,sum((date_format(hiduke,'%m')='06')*suuchi) as m6
from tbl,t_zokusei
where date_format(hiduke,'%Y')='2017'
group by kaisha asc ,zokusei2 asc with rollup
) as t1
left join t_kaisha on coalesce(kaisha,999)=kid
left join t_zokusei on coalesce(zokusei2,999)=zid
order by kaisha,zokusei2;

[yambejp]

from tbl,t_zokuseiがもともと間違ってたみたいですね
from tbl よかったんだと思います

[marshmallowy]

ありがとうございました。
確認ができました。

Answer 2

ちょっとSQLが冗長なので、以下を提示のSQLとします。

SELECT 
    CASE  ～
    ,sum.`1月`　～
FROM (
    SELECT 
         `orderer_id`
        ,`point_of_sale_id`
        ,SUM　～各月
    FROM ～
    WHERE ～
    GROUP BY 
         orderer_id
        ,point_of_sale_id
    WITH ROLLUP 
) AS sum; 

上記とほぼ同じ構造の以下のSQLとUNIONすれば取り敢えずはできそうですけど、冗長すぎるのでスルーして下さい。

SELECT 
    CASE  
        WHEN (`orderer_id`= 2) THEN 'F  社 - 店舗・店舗免税' 
        WHEN (`orderer_id`= 3) THEN 'R  社 - 店舗・店舗免税' 
        WHEN (`orderer_id`= 4) THEN 'C  社 - 店舗・店舗免税' 
    END AS '発注元の会社 - 販売した場所' 
    ,sum.`1月`　～
FROM (
    SELECT 
         `orderer_id`
        ,Null as `point_of_sale_id`
        ,SUM　～各月
    FROM ～
    WHERE ～
      and `point_of_sale_id` in (2,3)
    GROUP BY 
         orderer_id
    WITH ROLLUP 
) AS sum; 

Comments

[marshmallowy]

宜しくお願いします。

[sazi]

冗長な部分を排除したもので追記しようとしましたが、yambejpさんが色々バリエーション出しているので、これで回答してもそれこそ冗長になるのでもういいかなと思っています。
group_concat()使用したものを考えていましたが、そんなにシンプルにはならなそうなので。

[marshmallowy]

かしこまりました。
お忙しい中、お時間を割いていただき、ありがとうございました。