CODEWITHSUNDEEP
sqlmysql
Adam
Adam

Reputation: 629

Sql query split by a date range

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?

Upvotes: 2

Views: 1999

Answers (5)

NITESH
NITESH

Reputation: 31

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

Upvotes: 3

Chris R
Chris R

Reputation: 833

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.

Upvotes: 3

Stanq
Stanq

Reputation: 46

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

Upvotes: 3

Andomar
Andomar

Reputation: 238296

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)

Upvotes: 3

yu_sha
yu_sha

Reputation: 4400

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

Upvotes: 0

Related Questions