MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


8.0.27


create table persons as 
SELECT 1 as id, 'jackie' as name 
union select 2, 'chan'
create table orders as 
select 1 as id, 1 as person_id, '2022-01-01' as `date`
union select 2, 1, '2022-01-02'
union select 3, 2, '2022-01-02'
union select 4, 2, '2022-01-03'
union select 5, 1, '2022-01-04'
union select 6, 1, '2022-01.05'
create table order_details as 
select 1 as id, 1 as order_id, 'apple' as description, 100 as price
union select 2, 1, 'orange', 80
union select 3, 1, 'mango', 90
union select 4, 3, 'grape', 50
union select 5, 3, 'apple', 100
union select 6, 4, 'apple', 100
union select 7, 5, 'orange', 80
union select 8, 5, 'mango', 90
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
LEFT JOIN order_details od ON od.order_id = o.id
GROUP BY o.id, p.name, o.date


order_id
person_name
order_date
count_order_detail
subtotal


1
jackie
2022-01-01
3
270

2
jackie
2022-01-02
0
null

3
chan
2022-01-02
2
150

4
chan
2022-01-03
1
100

5
jackie
2022-01-04
2
170

6
jackie
2022-01.05
0
null


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


PERSON_NAME
ORDERS
ERROR_ORDER


jackie
4 Orders
2: 2,6

chan
2 Orders
null

order_id	person_name	order_date	count_order_detail	subtotal
1	jackie	2022-01-01	3	270
2	jackie	2022-01-02	0	null
3	chan	2022-01-02	2	150
4	chan	2022-01-03	1	100
5	jackie	2022-01-04	2	170
6	jackie	2022-01.05	0	null