Solving a query structure issue-CodePudding

In the following query I have to use the commented row on the top in the same time I have to keep those two columns from the picture in this format(grouped by month).

https://i.stack.imgur.com/3uabV.png

select  --REPLACE(SUBSTR(p.prod_desc,20,200),'size XXXL','size 14.00') ||':PRODUCT_ID='||p.PROD_ID as "PRODUCT_DESCRIPTION",
CASE WHEN t.CALENDAR_WEEK_NUMBER = 13 THEN 'First week'
         WHEN t.CALENDAR_WEEK_NUMBER = 14 THEN 'Second week'
         WHEN t.CALENDAR_WEEK_NUMBER = 15 THEN 'Third week'
         WHEN t.CALENDAR_WEEK_NUMBER = 16 THEN 'Fourth week'
         WHEN t.CALENDAR_WEEK_NUMBER = 17 THEN 'Fifth week'
    END as "WEEK_IN_MONTH",
        sum(s.AMOUNT_SOLD) as TOTAL_AMOUNT_SOLD   
from TIMES t join SALES s
    on t.TIME_ID = s.TIME_ID
  JOIN PRODUCTS p
    on p.PROD_ID = s.PROD_ID
where t.CALENDAR_MONTH_DESC = '2000-04'
      and p.PROD_ID in (300,10,540)
group by t.CALENDAR_WEEK_NUMBER
HAVING sum(s.AMOUNT_SOLD) > 0;

CodePudding user response：

If I understood you correctly, your problem is that - if you uncomment the replace piece of that code - you have to include it into the group by clause.

Literally:

  SELECT    REPLACE (SUBSTR (p.prod_desc, 20, 200), 'size XXXL', 'size 14.00')
         || ':PRODUCT_ID='
         || p.prod_id AS "PRODUCT_DESCRIPTION",
         CASE
            WHEN t.calendar_week_number = 13 THEN 'First week'
            WHEN t.calendar_week_number = 14 THEN 'Second week'
            WHEN t.calendar_week_number = 15 THEN 'Third week'
            WHEN t.calendar_week_number = 16 THEN 'Fourth week'
            WHEN t.calendar_week_number = 17 THEN 'Fifth week'
         END AS "WEEK_IN_MONTH",
         SUM (s.amount_sold) AS total_amount_sold
    FROM times t
         JOIN sales s ON t.time_id = s.time_id
         JOIN products p ON p.prod_id = s.prod_id
   WHERE     t.calendar_month_desc = '2000-04'
         AND p.prod_id IN (300, 10, 540)
GROUP BY t.calendar_week_number,
            REPLACE (SUBSTR (p.prod_desc, 20, 200),
                     'size XXXL',
                     'size 14.00')
         || ':PRODUCT_ID='
         || p.prod_id
  HAVING SUM (s.amount_sold) > 0;

Another option is to aggregate the replace part of code which would then eliminate need of having it in the group by:

  SELECT MAX (
               REPLACE (SUBSTR (p.prod_desc, 20, 200),
                        'size XXXL',
                        'size 14.00')
            || ':PRODUCT_ID='
            || p.prod_id) AS "PRODUCT_DESCRIPTION",
         CASE
            WHEN t.calendar_week_number = 13 THEN 'First week'
            WHEN t.calendar_week_number = 14 THEN 'Second week'
            WHEN t.calendar_week_number = 15 THEN 'Third week'
            WHEN t.calendar_week_number = 16 THEN 'Fourth week'
            WHEN t.calendar_week_number = 17 THEN 'Fifth week'
         END AS "WEEK_IN_MONTH",
         SUM (s.amount_sold) AS total_amount_sold
    FROM times t
         JOIN sales s ON t.time_id = s.time_id
         JOIN products p ON p.prod_id = s.prod_id
   WHERE     t.calendar_month_desc = '2000-04'
         AND p.prod_id IN (300, 10, 540)
GROUP BY t.calendar_week_number
  HAVING SUM (s.amount_sold) > 0;