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.
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
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?