How can i join different filtered areas in 1 table-CodePudding

I'm trying to find logistic company and cargo company combined performances from a table named orders. I write a working code but it is working slowly, i wanted it to work faster.

The working code is ;

'SELECT 
a.*,
b.number_of_order_lost_or_damaged,
c.number_of_order_lost_tracking FROM
(SELECT 
    logistic_partner,
        cargo_partner,
        round(AVG(DATEDIFF(date_delivered, date_add)),2) as time_spent_in_delivery
FROM
    orders
WHERE
    status IN (6)
GROUP BY logistic_partner , cargo_partner) a
    JOIN
(SELECT 
    logistic_partner,
        cargo_partner,
        COUNT(id) AS number_of_order_lost_or_damaged
FROM
    orders
WHERE
    status IN (8)
GROUP BY logistic_partner , cargo_partner) b USING (logistic_partner , cargo_partner)
    JOIN
(SELECT 
    logistic_partner,
        cargo_partner,
        COUNT(id) AS number_of_order_lost_tracking
FROM
    orders
WHERE
    status IN (10)
GROUP BY logistic_partner , cargo_partner) c USING (logistic_partner , cargo_partner);'

Not working code is

'SELECT 
o.logistic_partner,
o.cargo_partner,
round(AVG(DATEDIFF(a.date_delivered, a.date_add)),2) as time_spent_in_delivery,
count(b.id),
count(c.id) FROM orders o
    JOIN orders a on a.id=o.id and a.status = 6
    JOIN orders b on o.id=b.id and b.status = 8 
    JOIn orders c on c.id=o.id and c.status = 10
GROUP BY logistic_partner , cargo_partner;'

But those are working seperately :

'SELECT 
o.logistic_partner,
o.cargo_partner,
round(AVG(DATEDIFF(a.date_delivered, a.date_add)),2) as time_spent_in_delivery FROM orders o
    JOIN orders a on a.id=o.id and a.status = 6
GROUP BY logistic_partner , cargo_partner;'

SELECT 
o.logistic_partner,
o.cargo_partner,
count(b.id) FROM orders o
    JOIN orders b on o.id=b.id and b.status = 8 
GROUP BY logistic_partner , cargo_partner;

What could be the problem ?

CodePudding user response：

My guess is you don't need any of those derived tables. Using conditional aggregation should produce the same result. For example:

SELECT logistic_partner
      , cargo_partner
      , ROUND(AVG(IF(status = 6, DATEDIFF(date_delivered, date_add), NULL)),2) AS time_spent_in_delivery
      , SUM( IF( status = 8, 1, 0) ) AS number_of_order_lost_or_damaged
      , SUM( IF( status = 10, 1, 0) ) AS number_of_order_lost_tracking
FROM  orders
GROUP BY logistic_partner
       , cargo_partner