-- 
-- 売上実績表（部課別）
-- 
CREATE OR REPLACE VIEW v_sales_actual_result_table_by_sec
AS 
SELECT
    category_lv2_id,   -- 中分類ID
    category_name_lv2,   -- 中分類名
    sales_date_y,
    sales_date_m,
    sales_date_ym,
    dept_id, -- 部ID
    dept_name, -- 部
    section_id, -- 課ID
    section_name, -- 課
    SUM(
        total_sales_amount
    ) AS 'sum_total_sales_amount', -- 総売上額(sd.line_type="1:通常,4:訂正"の時に表示)
    SUM(
        return_amount
    ) AS 'sum_return_amount', -- 返品額(sd.line_type="5:返品"の時に表示)
    SUM(
        normal_discount_amount
    ) AS 'sum_normal_discount_amount', -- 通常値引額(sd.line_type="2:値引"の時に表示)
    SUM(
        policy_discount_amount
    ) AS 'sum_policy_discount_amount', -- 政策値引額(sd.line_type="3:政策値引"の時に表示)
    SUM(
        normal_discount_amount
        + policy_discount_amount
    ) AS 'sum_discount_amount', -- 値引額
    SUM(
        pure_sales
    ) AS 'sum_pure_sales',  -- 純売上額
    SUM(
        gross_profit
    ) AS 'gross_profit_amount', -- 粗利額
    ROUND(
        (
            SUM(gross_profit)
            /
            SUM(pure_sales)
        )
    , 1) AS 'gross_profit_rate' -- 粗利率(粗利額/純売上額 四捨五入)


-- ｜売上実績表
FROM v_sales_actual_result_table

GROUP BY 
    category_lv2_id,   -- 中分類ID
    category_name_lv2,   -- 中分類名
    dept_id,   -- 部ID
    dept_name,   -- 部名
    section_id,   -- 課ID
    section_name,   -- 課
    sales_date_y,
    sales_date_m,
    sales_date_ym