Harsh Sanghani Harsh Sanghani - 5 months ago 14
MySQL Question

Need to fetch record for each month in MYSQL

I need help to fetch record from the database with some condition as I explain below :-

I need to check the student availability for each month between selected startDate and end Date.

Table Structure :-

enter image description here

Table data :-

enter image description here

Example :-

Here you can take capacity of classroom as 20 students for example.

I want to check the seat availablity from 02/2016 to 04/2017.

Output will be :

02/2016 - 20
03/2016 - 19
04/2016 - 18
05/2016 - 15
06/2016 - 20
.
.
.
02/2017 - 14
03/2017 - 20
04/2017 - 18


thanks in advance for help.

Answer

I found your question like this if there are number of students and they can enroll any time in month this is not considered but yes if you can decide that will compare to any one date like startDate or endDate this query will definitely help you.

SELECT count(*) as cnt,CONCAT(MONTH(startDate),'/',  YEAR(startDate)) as day from notes group by day 

I considered with startDate.

Please let me know if i need more research .

khajaamin