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
| With Ordre (c_id, donneur) AS
(
select 174, 'OF001' union all
select 4, 'OF001' union all
select 5, 'OF001'
)
, Commandes (c_id, col) AS
(
select 174, 100 union all
select 174, 200 union all
select 174, 300 union all
select 4, 100 union all
select 4, 200 union all
select 5, 100
)
select O.donneur, O.c_id, count(*) as nb_commandes
from Ordre as O
inner join Commandes as C
on C.c_id = O.c_id
group by O.donneur, O.c_id
order by nb_commandes desc;
donneur c_id nb_commandes
------- ----------- ------------
OF001 174 3
OF001 4 2
OF001 5 1 |
Partager