Generating statistics with mysql

Question

How can I write an sql statement that returns a list of how many students had lessons for each of the last 12 months?

so I would expect output something like:

+--------+----------------+
| Month  | No of Students |
+--------+----------------+
| Oct 08 |  12            |
| ...    |  ...           |
| ...    |  ...           |
| Sep 09 |  15            |
+-------------------------+

edit: relevant schema -

lessons{student_id:INT, time:DATE}
student{id:INT, person_id:INT}

Students may take any number of lessons in a given month, so I'm not interested in how many lessons were in a month but how many students took lessons in that month

Answer 1

CREATE TABLE tmp (student_id int(10), month int(4), year int(4));

INSERT INTO tmp
SELECT DISTINCT student.id, MONTH(time), YEAR(time)
FROM lessons INNER JOIN student ON lessons.student_id = student.id
WHERE PERIOD_ADD(time,12) > NOW();

SELECT count(*) FROM tmp
GROUP BY MONTH(month), YEAR(year);

DROP TABLE tmp;

Answer 2

You can use date_format inside of a group by to achieve the results you're looking for, since you just want to do a count distinct.

select
    date_format(`time`, '%b %y') as `Month`,
    count(distinct student_id) as NumberOfStudents
from
    lessons
where
    `time` >= date_add(date_add(current_date interval -12 months) 
                  interval (-1)*(day(current_date)+1) days)
group by
    date_format(`time`, '%b %y')

If there can be multiple student_id for one person_id, and what you're really interested in is the count of unique person_ids, then you can do this:

select
    date_format(l.`time`, '%b %y') as `Month`,
    count(distinct s.person_id) as NumberOfPeople
from
    lessons l
    inner join students s on
        l.student_id = s.id
where
    l.`time` >= dateadd(dateadd(current_date interval -12 months) 
                  interval (-1)*(day(current_date)+1) days)
group by
    date_format(l.`time`, '%b %y')