1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22
| with matable as
(
select 'A1' cli, 'POMME' art, 'FRUIT' cat from dual union all
select 'A1' , 'POIRE' , 'FRUIT' from dual union all
select 'A1' , 'CHOUX' , 'LEGUME' from dual union all
select 'A2' , 'CHOUX' , 'LEGUME' from dual union all
select 'A3' , 'CAROTTE' , 'LEGUME' from dual
)
select cat_d.cat,
count(sr.cat_max) as nb_cli
from (select distinct cat from matable) cat_d -- référence des catégories
left outer join
( select max(cat) as cat_max
from matable
group by cli
having count(distinct cat) = 1) sr
on sr.cat_max = cat_d.cat
group by cat_d.cat;
CAT NB_CLI
LEGUME 2
FRUIT 0 |
Partager