sql - Left join command is not showing all results

Question

I have a table RESTAURANT:

Id | Name
------------------
0  | 'McDonalds'
1  | 'Burger King'
2  | 'Starbucks'
3  | 'Pans'

And a table ORDER:

Id | ResId | Client
--------------------
0  | 1     | 'Peter'
1  | 2     | 'John'
2  | 2     | 'Peter'

Where 'ResId' is a foreign key from RESTAURANT.Id.

I want to select the number of order per restaurant: Expected result:

Restaurant      | Number of orders
----------------------------------
'McDonalds'     | 0
'Burguer King'  | 1
'Starbucks'     | 2
'Pans'          | 0

Actual result:

Restaurant      | Number of orders
----------------------------------
'McDonalds'     | 0
'Burguer King'  | 1
'Starbucks'     | 2

Command used:

select r.Name, count(o.ResId) 
from RESTAURANT r 
  left join ORDER o on r.Id like o.ResId 
group by o.ResId;   

Answer 1

Just fix the group by clause:

select r.name, count(*)  as cnt_orders
from restaurants r 
left join orders o on r.id = o.resid 
group by r.id, r.name;

That way, the SELECT and GROUP BY clauses are consistent; I also added the restaurant id to the group, so potential restaurants having the same name are not aggregated together. I also changed like to =: this is more efficient, and does not alter the logic.

You could also phrase this with a subquery, so there is no need for outer aggregation. I would prefer:

select r.*,
    (select count(*) from orders o where o.resid = r.id) as cnt_orders
from restaurants r