質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

89.23%

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

解決済

回答 2

投稿 編集

  • 評価
  • クリップ 0
  • VIEW 1,364

marshmallowy

score 196

前提・実現したいこと

その年の売上を月毎に算出してます。
また、会社、販売場所をグループ化しています。

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

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

現在の状態

イメージ説明

該当のソースコード

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

  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 2

checkベストアンサー

+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;

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/09/04 19:21

    [サンプルコード]
    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;

    キャンセル

  • 2017/09/04 20:25

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

    キャンセル

  • 2017/09/05 09:54

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

    キャンセル

0

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

SELECT 
    CASE  ~
    ,sum.`1月` ~
FROM (
    SELECT 
         `orderer_id`
        ,`point_of_sale_id`
        ,SUM ~各月
    FROMWHEREGROUP 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 ~各月
    FROMWHEREand `point_of_sale_id` in (2,3)
    GROUP BY 
         orderer_id
    WITH ROLLUP 
) AS sum; 

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2017/09/01 20:12

    宜しくお願いします。

    キャンセル

  • 2017/09/02 01:03

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

    キャンセル

  • 2017/09/02 23:29

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

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 89.23%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる