MySQL Select Count of Duplicate Value In Relation Table

I have 3 tables: foods, order_detail, and orders

Here are the records for table foods:

id    | name       | type
------------------------------
F01   | Omelette   | Breakfast
F02   | Burger     | Breakfast
F03   | Satay      | Lunch
F04   | Fried Rice | Dinner

Here are the records for table order_detail:

food_id    | order_id
-----------------------------
F01        | T01
F04        | T01
F02        | T02
F03        | T03
F03        | T04

And here are the records for orders table:

order_id    | date       | qty
---------------------------------
T01         | 2017-05-01 | 2
T02         | 2017-05-02 | 1
T03         | 2017-05-05 | 1
T04         | 2017-05-07 | 1

I want to show count order detail grouped by food type. I expected this result:

type       | total_order
-------------------------
Breakfast  | 2
Lunch      | 2
Dinner     | 1

Here is my approach, but it still doesn’t show the expected result.

SELECT
f.type,
(SELECT COUNT(*) FROM order_detail od WHERE f.id = od.food_id) AS total_order
FROM foods f
LEFT JOIN order_detail od ON f.id = od.food_id
GROUP BY f.type
ORDER BY f.id

The result is:

type       | total_order
-------------------------
Breakfast  | 1
Lunch      | 2
Dinner     | 1

How can I get the result I want? Thanks in advance!

Answer

Aggregation can work here, but you need to join across all three tables:

SELECT f.type, COUNT(o.order_id) AS total_order
FROM foods f
LEFT JOIN order_detail od ON od.food_id = f.id
LEFT JOIN orders o ON o.order_id = od.order_id
GROUP BY f.type
ORDER BY f.id;

Note that we do a left join across all three tables in order to not drop any food type which might happen to have zero orders.