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;