MySQL Select Count of Duplicate Value In Relation Table-CodePudding

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!

CodePudding user response：

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.

CodePudding user response：

you can add the orderdtails to the subselect to get the correct number

SELECT
f.type,
(SELECT COUNT(*) FROM order_detail od2 
 WHERE f.id = od2.food_id AND od2.order_id = od.order_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

CodePudding user response：

WITH cte AS (
    SELECT type
    FROM foods
    INNER JOIN order_detail ON (order_detail.food_id=foods.id)
    INNER JOIN orders ON (order_detail.ord_id=orders.ord_id)
)
SELECT DISTINCT type, COUNT(type) OVER (PARTITION BY type) AS qty
FROM cte