37665


**前提・実現したいこと**

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

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

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

**補足情報**

MySQL 5.7

**現在の状態**

[![画像の説明をここに入力](https://i.stack.imgur.com/MaQum.png)](https://i.stack.imgur.com/MaQum.png)

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

[code]

    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; 
    
[/code]

**ビュー**

[code]

    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)
    
[/code]


CC BY-SA 3.0
2017-09-01T10:22:02.053
37662
2017-09-01T13:04:09.687
5731
question
mysql sql database
条件が一致したとき、合計値を算出する


以下のようなSQLで実現できると思います

[code]

    SELECT
        CASE
            -- mysql は 集約関数外で 集約対象カラム を利用すると 値が1つランダムに手に入る
            -- (だいたい先頭の値、保証はなく実装依存)
            -- なので、店舗 + 店舗免税 の集約をしたカラムの point_of_sale_id は [2, 3] のどちらか
            -- キチンとする(PostgreSQLとかでも動くようにする)なら
            -- MAX(point_of_sale_id) AS point_of_sale_id_agg
            -- のようにする
            WHEN (`orderer_id`= 2 AND `point_of_sale_id`=2) OR
                 (`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) OR
                 (`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) OR
                 (`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(sum.`1月`)
        -- ...
        ,SUM(sum.`12月`)
    FROM (
        -- ...
    ) AS sum
    GROUP BY
      `orderer_id`,
      CASE point_of_sale_id
      WHEN 2 THEN 2 -- 店舗(店舗免税と同じ値)
      WHEN 3 THEN 2 -- 店舗免税(店舗と同じ値)
             ELSE point_of_sale_id -- それ以外
      END
    ;
    
[/code]


CC BY-SA 3.0
2017-09-01T13:04:09.687
37665
2017-09-01T13:04:09.687
9796
37662
answer