sql query - calculating commission over 12 months

Question

I'm trying to write a query to work out how much commission that my client's company earns in a month as below

SELECT (rate * SUM(duration) / 180) as commission
        FROM   teacher, person, lesson 
        WHERE  person.id = teacher.person_id
        AND    lesson.teacher = person.id
        AND    MONTH(start_time) = MONTH(NOW())
        GROUP BY person.id

This is fine for working out this month, but how would I do this to give results for the past 12 months?

Answer 1

Use GROUP BY person.id, MONTH(start_time) and change the last AND clause in the WHERE into MONTH(NOW()) - MONTH(start_time) <= 12.