Not getting proper data while join two query in Mysql

Hi Everyone i am writing a query one query getting weekly data one is getting yesterday record from same table, when i execute both query separatally its working properly but when join both query then yesterday data query not getting proper output.

Sampal data

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=8a678d12df7643000838c2f442d4d85a

query 1- weekly data

SELECT temp2.driver_id,temp2.driver_name,GROUP_CONCAT(DISTINCT temp2.car_number) as car_number,
sum(temp2.day1_trips) as day1_trips,
sum(temp2.day2_trips) as day2_trips,
sum(temp2.day3_trips) as day3_trips,
sum(temp2.day4_trips) as day4_trips,
sum(temp2.day5_trips) as day5_trips,
sum(temp2.day6_trips) as day6_trips,
sum(temp2.day7_trips) as day7_trips,
sum(temp2.trips) as total_trips,
sum(temp2.revenue) as total_revenue,
sum(temp2.online_hours) as total_online_hours
FROM
(SELECT temp1.driver_id,temp1.driver_name,temp1.car_number,temp1.car_id,temp1.trips,temp1.revenue,temp1.online_hours,
case when temp1.date='2022-04-04' then temp1.trips else 0 end as day1_trips,
case when temp1.date='2022-04-05' then temp1.trips else 0 end as day2_trips,
case when temp1.date='2022-04-06' then temp1.trips else 0 end as day3_trips,
case when temp1.date='2022-04-07' then temp1.trips else 0 end as day4_trips,
case when temp1.date='2022-04-08' then temp1.trips else 0 end as day5_trips,
case when temp1.date='2022-04-09' then temp1.trips else 0 end as day6_trips,
case when temp1.date='2022-04-10' then temp1.trips else 0 end as day7_trips
from
(
   SELECT date,driver_id,driver_name,car_number,car_id,trips,revenue,online_hours
   FROM fleet_driver_dash_daily 
   WHERE  team_id=1 and (date BETWEEN '2022-04-04' and '2022-04-10'))as temp1
) as temp2 
GROUP by temp2.driver_id

quary-2 yesterday data

SELECT driver_id,
SUM(CASE date WHEN subdate(CURDATE(), 1) THEN trips ELSE 0 END) AS yesterday_trips,
SUM(CASE date WHEN subdate(CURDATE(), 1) THEN online_hours ELSE 0 END) AS yesterday_online,
SUM(CASE date WHEN subdate(CURDATE(), 1) THEN revenue ELSE 0 END) AS yesterday_revenue
FROM fleet_driver_dash_daily
WHERE team_id=1 
GROUP BY driver_id

expected output,

driver_id	car_number	day1_trip	day2_trip	day3_trip	day4_trip	day5_trip	yesterday_trip
10	00001	7	0	8	0		10
20	00002	0	0	10	0	5	0

Answer

I think you just want

 SELECT sub1.*, sub2.yesterday_trip
 FROM (
   -- put contents of first query here
 ) as sub1
 LEFT JOIN (
   -- put contents of second query herre
 ) as sub2 on sub1.driver_id = sub2.driver_id

Is there any reason this did not work?

Advertisement

Answer