Please help.
I am trying to add a description column to my query but the description should not be part of the group by column and this is giving incorrect percentage to ratio calculation.
This is the data in my table
When I run my query, this is what I get
This is the query
WITH unpivoted_parts AS
(
SELECT rep_id, product, fault_code
, part
, ROW_NUMBER () OVER ( PARTITION BY rep_id
ORDER BY part
) AS rn
FROM table_c
UNPIVOT ( part
FOR col IN (part1, part2, part3, part4, part5)
)
)
SELECT product, fault_code
, p1, p2, p3, p4, p5
, COUNT (*) AS qty_repair
, 100 * RATIO_TO_REPORT (COUNT (*))
OVER (PARTITION BY product, fault_code) AS percentage
FROM unpivoted_parts
PIVOT ( MIN (part)
FOR rn IN ( 1 AS p1
, 2 AS p2
, 3 AS p3
, 4 AS p4
, 5 AS p5
)
)
GROUP BY product, fault_code
, p1, p2, p3, p4, p5
ORDER BY product, fault_code -- or whatever you want
, p1 NULLS FIRST
, p2 NULLS FIRST
, p3 NULLS FIRST
, p4 NULLS FIRST
, p5 NULLS FIRST
;
This query and the result above is correct but I need to add the description column for the fault code "fault Desc" keeping the above result. Then I added this to the query and this is giving a wrong calculation because I added it column to the group by columns.
This is my new query with "fault_desc"
WITH unpivoted_parts AS
(
SELECT rep_id, product, fault_code, fault_desc
, part
, ROW_NUMBER () OVER ( PARTITION BY rep_id
ORDER BY part
) AS rn
FROM table_c
UNPIVOT ( part
FOR col IN (part1, part2, part3, part4, part5)
)
)
SELECT product, fault_code,fault_desc
, p1, p2, p3, p4, p5
, COUNT (*) AS qty_repair
, 100 * RATIO_TO_REPORT (COUNT (*))
OVER (PARTITION BY product, fault_code) AS percentage
FROM unpivoted_parts
PIVOT ( MIN (part)
FOR rn IN ( 1 AS p1
, 2 AS p2
, 3 AS p3
, 4 AS p4
, 5 AS p5
)
)
GROUP BY product, fault_code, fault_desc
, p1, p2, p3, p4, p5
ORDER BY product, fault_code -- or whatever you want
, p1 NULLS FIRST
, p2 NULLS FIRST
, p3 NULLS FIRST
, p4 NULLS FIRST
, p5 NULLS FIRST
;
This is the result which is not correct
Sample Data used
-- Create table
create table TABLE_C
(
rep_id NUMBER(8),
product NUMBER(8),
fault_code NUMBER(3),
part1 NUMBER(8),
part2 NUMBER(8),
part3 NUMBER(8),
part4 NUMBER(8),
part5 NUMBER(8),
fault_desc VARCHAR2(100)
)
tablespace USERS
pctfree 10
initrans 1
maxtrans 255
storage
(
initial 64K
next 1M
minextents 1
maxextents unlimited
);
REM INSERTING into TABLE_C
SET DEFINE OFF;
Insert into TABLE_C (REP_ID,PRODUCT,FAULT_CODE,PART1,PART2,PART3,PART4,PART5,FAULT_DESC) values (70507,31100644,2,40007004,null,null,null,null,'Ne susi');
Insert into TABLE_C (REP_ID,PRODUCT,FAULT_CODE,PART1,PART2,PART3,PART4,PART5,FAULT_DESC) values (70508,31100644,2,40007003,40007004,49042220,null,null,'Non asciuga');
Insert into TABLE_C (REP_ID,PRODUCT,FAULT_CODE,PART1,PART2,PART3,PART4,PART5,FAULT_DESC) values (70509,31100644,2,40006767,40007003,40007004,41039164,null,'Non asciuga');
Insert into TABLE_C (REP_ID,PRODUCT,FAULT_CODE,PART1,PART2,PART3,PART4,PART5,FAULT_DESC) values (70510,31100644,2,40007004,null,null,null,null,'Ne susi');
Insert into TABLE_C (REP_ID,PRODUCT,FAULT_CODE,PART1,PART2,PART3,PART4,PART5,FAULT_DESC) values (70511,31100644,2,41039164,null,null,null,null,'Non asciuga');
Insert into TABLE_C (REP_ID,PRODUCT,FAULT_CODE,PART1,PART2,PART3,PART4,PART5,FAULT_DESC) values (70512,31100644,2,40007004,null,null,null,null,'Non asciuga');
Insert into TABLE_C (REP_ID,PRODUCT,FAULT_CODE,PART1,PART2,PART3,PART4,PART5,FAULT_DESC) values (70513,31100644,2,43009052,91943334,null,null,null,'Non asciuga');
Insert into TABLE_C (REP_ID,PRODUCT,FAULT_CODE,PART1,PART2,PART3,PART4,PART5,FAULT_DESC) values (70514,31100644,2,40007004,91943334,null,null,null,'Non asciuga');
I don't want to group by "fault_desc"
Many thanks for your help.