Query to combine two tables group by month-CodePudding

I have tried to connect two tables by join and group them to get the count. But unfortunately, these two tables don't have any common value to join (Or I have misunderstood the solutions).

select date_format(check_in.date,'%M') as Month, count(check_in.Id) as checkInCount 
from check_in 
group by month(check_in.date);

Month	checkInCount
July	1
October	2

This is the first table.

select date_format(reservation.date,'%M') as Month, count(reservation.id) as reserveCount 
from reservation
group by month(reservation.date);

Month	reserveCount
July	3
October	5

This is the second table. I want to show these two tables in one table.

Month	checkInCount	reserveCount
July	1	3
October	2	5

Thank you for trying this and sorry if this is too easy.

CodePudding user response：

You will need to join the result by month from your two subqueries. This query assume all the month (July, August, September...) present in your subqueries monthCheckInStat, monthCheckOutStat, even if the count is 0

SELECT monthCheckInStat.Month, monthCheckInStat.checkInCount, monthCheckOutStat.reserveCount
FROM
(
    select date_format(check_in.date,'%M') as Month, count(check_in.Id) as checkInCount 
    from check_in 
    group by month(check_in.date)
) monthCheckInStat
INNER JOIN
(
    select date_format(reservation.date,'%M') as Month, count(reservation.id) as reserveCount 
    from reservation
    group by month(reservation.date)
) monthCheckOutStat
ON monthCheckInStat.Month = monthCheckOutStat.Month;