sql - Getting the Monthwise count from date column in MySQL

Question

I have a table that consists of the following data, I would like to know whether it is possible to get a Month (i.e Jan, Feb) wise count of Reservations that happened and also Month wise count for each location.

PNR	Location	Reservation Date	Passenger Name	Travel Date
PNR81239087	Mumbai	2019-10-01 12:19:00	Ram	2019-11-06 15:59:00
PNR81239090	Kerala	2019-10-01 15:18:00	Kannan	2019-12-03 19:18:00
PNR812390199	Mumbai	2019-10-01 17:19:00	Ram	2019-11-01 18:39:00

Answer 1

I think this will work for you

Month Wise Count (including all locations) :

select MONTHNAME(Reservation_Date) as Month, count(*)
from yourTable
group by MONTHNAME(Reservation_Date)

Monthwise count for each location :

select MONTHNAME(Reservation_Date) as Month, count(*), Location
from yourTable
group by MONTHNAME(Reservation_Date), Location

EDIT IN QUESTION :

Changed to Group by Year and Month in one column:

Month Wise Count (including all locations) :

select concat(MONTHNAME(Reservation_Date),'-',Year(Reservation_Date)) as Month-Year,
       count(*) as Count
from yourTable
group by concat(MONTHNAME(Reservation_Date),'-',Year(Reservation_Date))

Monthwise count for each location :

select concat(MONTHNAME(Reservation_Date),'-',Year(Reservation_Date)) as Month-Year,
       count(*) as Count, Location
from yourTable
group by concat(MONTHNAME(Reservation_Date),'-',Year(Reservation_Date)), Location