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
| WITH Article AS
(
select 1 as IdArticle, 'Article 1' as noArticle from dual union all
select 2 , 'Article 2' from dual union all
select 3 , 'Article 3' from dual union all
select 4 , 'Article 4' from dual union all
select 5 , 'Article 5' from dual
)
, LigneCommande AS
(
select 1 as lcoid, 1 as IdArticle, 3 as quantite from dual union all
select 2 , 1 , 2 from dual union all
select 3 , 2 , 0 from dual union all
select 4 , 5 , 1 from dual union all
select 5 , 4 , 2 from dual union all
select 6 , 4 , 7 from dual
)
select
sum(case when coalesce(sum(lco.quantite), 0) < 2 then 1 else 0 end) as NOMBREIMPOPULAIRES,
sum(case when coalesce(sum(lco.quantite), 0) > 5 then 1 else 0 end) as NOMBREPOPULAIRES
from
article art
left outer join lignecommande lco
on lco.idarticle = art.idarticle
group by
art.noarticle;
NOMBREIMPOPULAIRES NOMBREPOPULAIRES
3 1 |
Partager