MYSQL or Laravel Eloquent How To Count The Detail From This Order

Question

[MYSQL QUERY / LARAVEL ELOQUENT QUESTION]

Hi, I really need some help with MYSQL Query this 3 tables I have. The order table and the details it's a manually input on our system.

I'm new in MYSQL, need a help not a judge.

PERSON TABLE (persons)

ID	PERSON_NAME
1	Jackie
2	Chan

ORDER TABLE (orders)

ID	PERSON_ID	ORDER_DATE
1	1 (Jackie)	January 01, 2022
2	1 (Jackie)	January 02, 2022
3	2 (Chan)	January 02, 2022
4	2 (Chan)	January 03, 2022
5	1 (Jackie)	January 04, 2022
6	1 (Jackie)	January 05, 2022

ORDER DETAIL TABLE (order_details)

ID	ORDER_ID	DESCRIPTION	PRICE
1	1	Apple	100
2	1	Orange	80
3	1	Mango	90
4	3	Grape	50
5	3	Apple	100
6	4	Apple	100
7	5	Orange	80
8	5	Mango	90

---

QUESTION

Here I combined the 3 tables, and this is what I got, it's showing Order ID 2 and 6 doesn't have any order detail added (manually) on our system let's say I forgot to add the detail. So I need to show it as an error on person page.

SELECT
  o.id as order_id,
  p.name as person_name,
  o.date as order_date,
  COUNT(od.id) as count_order_detail,
  SUM(od.price) as subtotal
FROM persons p
JOIN orders o ON o.person_id = p.id
JOIN order_details od ON od.order_id = o.id
GROUP BY o.order_id

ORDER_ID	PERSON_NAME	ORDER_DATE	COUNT_ORDER_DETAIL	SUBTOTAL
1	Jackie	January 01, 2022	3	270
2	Jackie	January 02, 2022	0	0
3	Chan	January 02, 2022	2	150
4	Chan	January 03, 2022	1	100
5	Jackie	January 04, 2022	2	170
6	Jackie	January 05, 2022	0	0

EXPECTED QUERY RESULT ON MYSQL TABLE

Here is the result I expected showing the result of order with no detail.

PERSON_NAME	ORDERS	ERROR_ORDER
Jackie	4 Orders	2
Chan	2 Orders	-

How can I do it ? I tried to COUNT the COUNT but it's messed up.

Note : Sorry for the bad grammar.

Answer 1

Well I could not get the hypen into the ERROR_ORDER when there are no errors, but this should be pretty close otherwise:

SELECT name AS PERSON_NAME, 
       CONCAT(count(id), ' Orders') AS ORDERS, 
       CONCAT(count(order_id), ': ', 
           GROUP_CONCAT(IF(ISNULL(order_id), id, NULL))) AS 'ERROR_ORDER'
FROM (
    SELECT name, o.id, order_id
      FROM persons p JOIN orders o ON o.person_id = p.id
        LEFT JOIN order_details od ON od.order_id = o.id
     GROUP BY name, o.id, order_id
) x
GROUP BY name
ORDER BY ORDERS DESC;

Which gives:

PERSON_NAME	ORDERS	ERROR_ORDER
jackie	4 Orders	2: 2,6
chan	2 Orders	null

...or by screenshot:

Example dbfiddle with all the data.

=====

But, if you really only want to show 2 in the last column, without the order references, then change the last part of the select to be:

CONCAT(SUM(IF(ISNULL(order_id), 1, null))) AS 'ERROR_ORDER'

Which would look like this:

PERSON_NAME	ORDERS	ERROR_ORDER
jackie	4 Orders	2
chan	2 Orders	null

...or by screenshot:

Example fiddle for this example.