Sql query split by a date range

Question

I want to count the number of applications by a student split by month since their first application.

Say I have the table structure as follows :

Student     ApplicationDate
-------     ---------------
Barry       2009-01-01
Barry       2009-01-20
Barry       2009-01-23
Barry       2009-02-01
Barry       2009-02-15
Barry       2009-03-01

I would like something along the lines of :

Student     Month     Applications
-------     -----     ------------
Barry       1/2009    3
Barry       2/2009    2
Barry       3/2009    1

How do you perform this in SQL for all students, for all applications?

Answer 1

select Student, month(ApplicationDate) ApplicationMonth, count(*) 
from table_name 
group by Student, ApplicationMonth

Answer 2

If I understand you correctly, this can be accomplished with a GROUP BY:

select 
    student, 
    year(ApplicationDate), 
    month(ApplicationDate), 
    count(*) as Applications
from YourTable
group by student, year(ApplicationDate), month(ApplicationDate)

Answer 3

To give you the exact output you specified, I think this'll work...

select Student,
       DATE_FORMAT(ApplicationDate,'%m/%Y') as 'Month',
       count(*) as 'Applications'
from tableName
group by Student, month(ApplicationDate), year(AppilcationDate)
order by year(ApplicationDate), month(ApplicationDate), Student

EDIT: Changed to use DATE_FORMAT function, as suggested by Stanq.

Answer 4

SELECT 
    student, 
    DATE_FORMAT(ApplicationDate,'%m/%Y') as Month
    count(id) as Applications
from YourTable
group by ApplicationDate