1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
| With RES as
(
select 1 as id_res, 'C76.4' as icd, '0' as consent, sysdate as sample_date from dual union all
select 2 , 'C76.4' , '0' , sysdate from dual union all
select 3 , 'C76.4' , '1' , sysdate from dual union all
select 4 , 'C76.4' , '1' , sysdate from dual union all
select 5 , 'C76.4' , '1' , sysdate from dual union all
select 6 , 'C76.4' , '1' , sysdate from dual union all
select 7 , 'C76.4' , '1' , sysdate from dual union all
select 8 , 'C26.9' , '1' , sysdate from dual union all
select 9 , 'C26.9' , '1' , sysdate from dual union all
select 10 , 'C26.9' , '1' , sysdate from dual
)
select count(*) as "Compte CODE en 2010",
case res.consent
when '1' then 'Oui'
when '0' then 'Non'
end as "reponse",
res.icd as "CODE",
case res.consent
when '1'
then round(100*RATIO_TO_REPORT(count(*)) OVER (partition by res.icd), 2)
end as "Pourcentage Oui"
from res
where trunc(res.sample_date, 'DD') > to_date('10/12/2010', 'DD-MM-YYYY')
and res.icd is not null
group by res.icd, res.consent
order by res.consent asc, count(res.icd) desc;
Compte CODE en 2010 rep CODE Pourcentage Oui
------------------- --- ----- ---------------
2 Non C76.4
5 Oui C76.4 71.43
3 Oui C26.9 100 |
