Hi
I have a table SC_CONFORMITE and I need to make a select appropriate.
CREATE TABLE "SC_CONFORMITE2"
( "ETAT_DE_CONFORMITE" VARCHAR2(255 BYTE),
"TYPE_DE_NON_CONFORMITE" VARCHAR2(255 BYTE));
Insert into SC_CONFORMITE2 (ETAT_DE_CONFORMITE,TYPE_DE_NON_CONFORMITE) values ('NC','Item 1');
Insert into SC_CONFORMITE2 (ETAT_DE_CONFORMITE,TYPE_DE_NON_CONFORMITE) values ('NC','Item 2');
Insert into SC_CONFORMITE2 (ETAT_DE_CONFORMITE,TYPE_DE_NON_CONFORMITE) values ('NC','Item 3');
Insert into SC_CONFORMITE2 (ETAT_DE_CONFORMITE,TYPE_DE_NON_CONFORMITE) values ('NC','Item 3');
Insert into SC_CONFORMITE2 (ETAT_DE_CONFORMITE,TYPE_DE_NON_CONFORMITE) values ('NC','Item 3');
Insert into SC_CONFORMITE2 (ETAT_DE_CONFORMITE,TYPE_DE_NON_CONFORMITE) values ('NC','Item 4');
Insert into SC_CONFORMITE2 (ETAT_DE_CONFORMITE,TYPE_DE_NON_CONFORMITE) values ('C par dérogation','Item 4');
Insert into SC_CONFORMITE2 (ETAT_DE_CONFORMITE,TYPE_DE_NON_CONFORMITE) values ('C par dérogation',null);
Insert into SC_CONFORMITE2 (ETAT_DE_CONFORMITE,TYPE_DE_NON_CONFORMITE) values ('C',null);
Insert into SC_CONFORMITE2 (ETAT_DE_CONFORMITE,TYPE_DE_NON_CONFORMITE) values ('C',null);
The result of the SQL statement should be :
ITEM PERCENT
---------------------------------------------
Item 1 10
Item 2 10
Item 3 30
Item 4 10
C par dérogation 20
C 20
I have tried this but without good result:
Select round(100*ratio_to_report(count(*)) over (), 2) Pourcentage, type_de_non_conformite
from SC_CONFORMITE
where etat_de_conformite = 'NC'
group by TYPE_DE_NON_CONFORMITE
union
Select round(100*ratio_to_report(count(*)) over (), 2) Pourcentage, ETAT_DE_CONFORMITE
from SC_CONFORMITE
where etat_de_conformite = 'C' or etat_de_conformite = 'C par dérogation'
group by ETAT_DE_CONFORMITE
order by 1 desc;
Thank you for your kind help. Regards.
Chipniz
The result of the query should be :