How to group sales by month, quarter and year in the same row using case?-CodePudding

I'm trying to return the total number of sales for every month, every quarter, for the year 2016. I want to display annual sales on the first month row, and not on the other rows. Plus, I want to display the quarter sales on the first month of each quarter, and not on the others.

To further explain this, here's what I want to achieve:

MONTH   MONTH_SALES QUARTER_SALES   YEAR_SALES
  1         2183        5917          12505
  2         1712         -              - 
  3         1972         -              - 
  4         2230        6588            - 
  5         2250         -              - 
  6         2108         -              -

Here's my SQL query so far:

SELECT
    Time.month,
    SUM(Sales.sales) AS MONTH_SALES, -- display monthly sales.
    CASE
        WHEN MOD(Time.month, 3) = 1 THEN ( -- first month of quarter
            SELECT
                SUM(Sales.sales)
            FROM
                Sales,
                Time
            WHERE
                Sales.Time_id = Time.Time_id
                AND Time.year = 2016
            GROUP BY
                Time.quarter
            FETCH FIRST 1 ROW ONLY
        )
    END AS QUARTER_SALES,
    CASE
        WHEN Time.month = 1 THEN ( -- display annual sales.
            SELECT
                SUM(Sales.sales)
            FROM
                Sales,
                Time
            WHERE
                Sales.Time_id = Time.Time_id
                AND Time.year = 2016
            GROUP BY
                Time.year
        )
    END AS YEAR_SALES
FROM
    Sales,
    Time
WHERE
    Sales.Time_id = Time.Time_id
    AND Time.year = 2016
GROUP BY
    Time.month
ORDER BY
    Time.month

I'm almost getting the desired output, but I'm getting the same duplicated 6588 value in quarter sales for the first and fourth month (because I'm fetching the first row that comes from first quarter).

MONTH   MONTH_SALES QUARTER_SALES   YEAR_SALES
  1         2183        6588          12505
  2         1712         -              - 
  3         1972         -              - 
  4         2230        6588            - 
  5         2250         -              - 
  6         2108         -              -

I even tried to put WHERE Time.quarter = ((Time.month * 4) / 12) but the month value from the outer query doesn't get passed in the subquery.

Unfortunately I don't have enough experience with CASE WHEN expressions to know how to pass the month row. Any tips would be awesome.

CodePudding user response：

How about this?

Sample data:

SQL> with
  2  time (time_id, month, quarter, year) as
  3    (select 1,  1, 1, 2016 from dual union all
  4     select 2,  2, 1, 2016 from dual union all
  5     select 3,  3, 1, 2016 from dual union all
  6     select 4,  5, 2, 2016 from dual union all
  7     select 5,  7, 3, 2016 from dual union all
  8     select 6,  8, 3, 2016 from dual union all
  9     select 7,  9, 3, 2016 from dual union all
 10     select 8, 10, 4, 2016 from dual union all
 11     select 9, 11, 4, 2016 from dual
 12    ),
 13  sales (time_id, sales) as
 14    (select 1, 100 from dual union all
 15     select 1, 100 from dual union all
 16     select 2, 200 from dual union all
 17     select 3, 300 from dual union all
 18     select 4, 400 from dual union all
 19     select 5, 500 from dual union all
 20     select 6, 600 from dual union all
 21     select 7, 700 from dual union all
 22     select 8, 800 from dual union all
 23     select 9, 900 from dual
 24    ),

Query begins here; it uses sum aggregate in its analytic form; partition by clause says what to compute. row_number, similarly, sorts rows in each quarter/year - it is later used in CASE expression to decide whether to show quarterly/yearly total or not.

 25  temp as
 26    (select t.month, t.quarter, t.year, sum(s.sales) month_sales
 27      from time t join sales s on s.time_id = t.time_id
 28      where t.year = 2016
 29      group by t.month, t.quarter, t.year
 30    ),
 31  temp2 as
 32    (select month, quarter, month_sales,
 33       sum(month_sales) over (partition by quarter) quarter_sales,
 34       sum(month_sales) over (partition by year   ) year_sales,
 35       row_number() over (partition by quarter order by quarter) rnq,
 36       row_number() over (partition by year    order by null)    rny
 37     from temp
 38    )
 39  select month,
 40    month_sales
 41    case when rnq = 1 then quarter_sales end month_sales,
 42    case when rny = 1 then year_sales end year_sales
 43  from temp2
 44  order by month;

     MONTH MONTH_SALES QUARTER_SALES YEAR_SALES
----------  ---------- ----------- ----------
         1        200         700       4600
         2        200
         3        300
         4        400        1500
         5        500        
         6        600
         7        700        2400
         8        800        
         9        900

9 rows selected.

SQL>