the tables i am using are retail_str_sales_detail,retail_store_prod,retail_store Actualy the main query this query is working but it is taking more time to execute so i tried to change that query into more efficient way
SELECT SET2.PROD_NM,SET2.TherapeuticClass,set2.TOTAL,SET2.QTY as QUANTITY,
set2.MFG as MFG,set2.monthname as MONTHNAME,set2.year as YEAR,
ROUND(((set2.TOTAL/SET3.TOTAL)*100),2) as SHARE
FROM (select
set1.PROD_NM AS PROD_NM,
set1.MFG AS MFG,
set1.monthname AS monthname,
set1.year AS year,
sum(set1.TOTAL) AS TOTAL,
sum(set1.qty) as QTY,
set1.TH_CLASS_1 AS TH_1,
set1.TH_CLASS_2 AS TH_2,
set1.TH_CLASS_3 AS TH_3,
set1.TH_CLASS_4 AS TH_4,
CONCAT(CONCAT(CONCAT(CONCAT( set1.TH_CLASS_1, ','),set1.TH_CLASS_2),','),CONCAT(CONCAT( set1.TH_CLASS_3, ','),set1.TH_CLASS_4) ) as TherapeuticClass
from
(select
retail_store_prod.TH_CLASS_4 as TH_CLASS_4,
retail_store_prod.TH_CLASS_3 as TH_CLASS_3,
retail_store_prod.MFG as MFG,
retail_store_prod.TH_CLASS_2 as TH_CLASS_2,
retail_store_prod.TH_CLASS_1 as TH_CLASS_1,
retail_store_prod.store_id as store_id ,
retail_store.str_nm,
sum(retail_Str_sales_detail.qty) as qty,
retail_Str_sales_detail.prod_nm as PROD_NM,
monthname(retail_str_sales_detail.sale_date) as monthname,
year(retail_str_sales_detail.sale_date) as year,
round(sum (retail_Str_sales_detail.total),2) AS TOTAL
from
retail_str_sales_detail ,
retail_store_prod,retail_store
where
retail_store_prod.prod_nm = retail_str_sales_detail.prod_nm and
retail_store_prod.store_id=retail_str_sales_detail.store_id and
retail_store.store_id = retail_store_prod.store_id
AND retail_store_prod.TH_CLASS_4 != 'NULL' AND retail_store_prod.TH_CLASS_3 != 'NULL'
AND retail_store_prod.TH_CLASS_2 != 'NULL' AND retail_store_prod.TH_CLASS_1 != 'NULL'
AND retail_store_prod.TH_CLASS_4 != '' AND retail_store_prod.TH_CLASS_3 != ''
AND retail_store_prod.TH_CLASS_2 != '' AND retail_store_prod.TH_CLASS_1 != ''
GROUP BY
retail_store_prod.TH_CLASS_4 ,retail_store_prod.TH_CLASS_3 ,retail_store_prod.MFG,retail_str_sales_detail.sale_date,
retail_store_prod.TH_CLASS_2 ,retail_store_prod.TH_CLASS_1,
retail_Str_sales_detail.prod_nm ,retail_store.str_nm,
retail_store_prod.store_id order by retail_Str_sales_detail.prod_nm,
retail_store_prod.TH_CLASS_4 ,retail_store_prod.TH_CLASS_3 ,
retail_store_prod.TH_CLASS_2 ,retail_store_prod.TH_CLASS_1 ,retail_store.str_nm,
round(sum (retail_Str_sales_detail.total),2) desc) as set1
group by set1.PROD_NM,set1.TH_CLASS_1,set1.TH_CLASS_2,set1.TH_CLASS_3,set1.TH_CLASS_4,set1.year,set1.monthname,set1.mfg,
CONCAT(CONCAT(CONCAT(CONCAT( set1.TH_CLASS_1, ','),set1.TH_CLASS_2),','),CONCAT(CONCAT( set1.TH_CLASS_3, ','),set1.TH_CLASS_4) )
order by set1.PROD_NM) as SET2
FULL OUTER JOIN
(select
sum(set1.TOTAL) AS TOTAL,sum(set1.qty) as QTY,
set1.TH_CLASS_1 AS TH_1,set1.TH_CLASS_2 AS TH_2,
set1.TH_CLASS_3 AS TH_3,set1.TH_CLASS_4 AS TH_4,
CONCAT(CONCAT(CONCAT(CONCAT( set1.TH_CLASS_1, ','),set1.TH_CLASS_2),','),CONCAT(CONCAT( set1.TH_CLASS_3, ','),set1.TH_CLASS_4) ) as TherapeuticClass
from
(select retail_store_prod.TH_CLASS_4 as TH_CLASS_4,retail_store_prod.TH_CLASS_3 as TH_CLASS_3,
retail_store_prod.TH_CLASS_2 as TH_CLASS_2,retail_store_prod.TH_CLASS_1 as TH_CLASS_1,
retail_store_prod.store_id as store_id ,retail_store.str_nm,sum(retail_Str_sales_detail.qty) as qty,
retail_Str_sales_detail.prod_nm as PROD_NM,round(sum (retail_Str_sales_detail.total),2) AS TOTAL
from
retail_str_sales_detail ,retail_store_prod,retail_store
where
retail_store_prod.prod_nm = retail_str_sales_detail.prod_nm and
retail_store_prod.store_id=retail_str_sales_detail.store_id and
retail_store.store_id = retail_store_prod.store_id
AND retail_store_prod.TH_CLASS_4 != 'NULL' AND retail_store_prod.TH_CLASS_3 != 'NULL'
AND retail_store_prod.TH_CLASS_2 != 'NULL' AND retail_store_prod.TH_CLASS_1 != 'NULL'
AND retail_store_prod.TH_CLASS_4 != '' AND retail_store_prod.TH_CLASS_3 != ''
AND retail_store_prod.TH_CLASS_2 != '' AND retail_store_prod.TH_CLASS_1 != ''
GROUP BY
retail_store_prod.TH_CLASS_4 ,retail_store_prod.TH_CLASS_3 ,
retail_store_prod.TH_CLASS_2 ,retail_store_prod.TH_CLASS_1 ,
retail_Str_sales_detail.prod_nm ,retail_store.str_nm,
retail_store_prod.store_id order by retail_Str_sales_detail.prod_nm,
retail_store_prod.TH_CLASS_4 ,retail_store_prod.TH_CLASS_3 ,
retail_store_prod.TH_CLASS_2 ,retail_store_prod.TH_CLASS_1 ,retail_store.str_nm,
round(sum (retail_Str_sales_detail.total),2) desc) as set1
group by set1.TH_CLASS_1,set1.TH_CLASS_2,set1.TH_CLASS_3,set1.TH_CLASS_4,
CONCAT(CONCAT(CONCAT(CONCAT( set1.TH_CLASS_1, ','),set1.TH_CLASS_2),','),CONCAT(CONCAT( set1.TH_CLASS_3, ','),set1.TH_CLASS_4) )
) AS SET3
ON
SET3.TH_1= SET2.TH_1 AND SET3.TH_2 = SET2.TH_2 AND
SET3.TH_3= SET2.TH_3 AND SET3.TH_4 = SET2.TH_4
and i changed it to In this query i am trying to get the share of a product on the basis of combined therapeutic class
SELECT prod_nm
, th_class_1 || ',' || th_class_2 || ',' ||
th_class_3 || ',' || th_class_4 AS therapeuticclass
, total
, qty
, mfg
, yearmonth/100 as year
, MONTHNAME(TO_DATE(yearmonth*100+1,'YYYYMMDD')) as monthname
, Round(((
total / SUM(total) OVER(PARTITION BY
th_class_4, th_class_3, th_class_2, th_class_1 )
)*100),2) AS share
FROM
( SELECT
sd.prod_nm
, sp.mfg
, sp.th_class_4
, sp.th_class_3
, sp.th_class_2
, sp.th_class_1
-- , sp.store_id
-- , rs.str_nm
, INTEGER(sd.sale_date)/100 AS yearmonth
, SUM(sd.qty) AS qty
, SUM(sd.total) AS total
FROM
retail_str_sales_detail sd
INNER JOIN retail_store_prod sp ON sd.prod_nm = sp.prod_nm
AND sd.store_id = sd.store_id
INNER JOIN retail_store rs ON rs.store_id = sp.store_id
WHERE
sp.th_class_4 NOT IN ('NULL','')
AND sp.th_class_3 NOT IN ('NULL','')
AND sp.th_class_2 NOT IN ('NULL','')
AND sp.th_class_1 NOT IN ('NULL','')
GROUP BY
sd.prod_nm
, sp.th_class_4
, sp.th_class_3
, sp.th_class_2
, sp.th_class_1
, sp.mfg
-- , sp.store_id
-- , rs.str_nm
, INTEGER(sd.sale_date)/100
) s
With these tables and data, the query returns two rows, so the query is OK. It must by your data that is at fault.
create table retail_str_sales_detail (prod_nm int, store_id int, sale_date date, qty int, total int);
create table retail_store_prod (mfg int, prod_nm int, store_id int, th_class_1 char(4), th_class_2 char(4), th_class_3 char(4), th_class_4 char(4));
create table retail_store (store_id int, str_nm varchar(20));
insert into retail_str_sales_detail values (1,1,current date,1,1),(2,1,current date,5,5);
insert into retail_store_prod values (1,1,1,1,1,1,1),(1,2,1,1,1,1,1);
insert into retail_store values (1,1);
Although if your total column is an INTEGER, you will need to convert it to a DECIMAL to get a non-zero share calc. E.g. DECIMAL(SUM(sd.total),19,2) AS total