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;'
or
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