how to get current week and last week record in same table with different rows in mysql

Question

Hi Everyone i am writing mysql query for getting current week and last week data in same table with different rows expected output i have mention below also i need last week data based on current week selection. current week data i am getting based on select date but didn't know how to get last week date, please help me out.

date	total_cars	online_hours	trips
current_week	20	100	200
last_week	10	60	80

query for current week record

SELECT cd.date, COUNT( cd.car_number) as total_cars, sum(cd.trips) as total_trips,
sum(cd.online_hours) as online_hours
FROM fleet_car_dash_daily as cd
WHERE cd.team_id= 1 and cd.date BETWEEN '2022-04-04' and '2022-04-10'

Answer 1

This can also help:

SELECT cd.date, COUNT( cd.car_number) as total_cars, sum(cd.trips) as total_trips,
sum(cd.online_hours) as online_hours
FROM fleet_car_dash_daily as cd
WHERE cd.team_id= 1 and cd.date BETWEEN '2022-04-04' - INTERVAL 7 DAY and '2022-04-10'
GROUP BY WEEK(cd.date)
ORDER BY WEEK(cd.date);