Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I am trying to group results by 3 months period starting by the current month as shown below:

row1      15 -- This should contain November, September and October
row2      25 -- This should contain August, July and June
row3      5  -- This should contain May, April and March
row4      2  -- This is should contain February and Janvier

I have no idea about how to accomplish this. Any help please?

So far I can group by month:

SELECT MONTH(date), MONTHNAME(date) as month, COUNT(*) 
FROM table_name 
WHERE MONTH(date) < NOW()  
GROUP BY MONTH(date) 
ORDER BY MONTH(date) DESC
share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

For the following I am assuming you already have a numbers table, If you don't have a numbers table, then I'd recommend you make one then, but if you don't want to then you can create a number list on the fly

With this numbers table you can then define your quarters:

SELECT  DATE_ADD(DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 MONTH) ,'%Y-%m-01'), INTERVAL -Number - 1 QUARTER) AS QuarterStart,
        DATE_ADD(DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 MONTH) ,'%Y-%m-01'), INTERVAL -Number QUARTER) AS QuarterEnd
FROM    Numbers

This gives (When run at the time of answering in November 2013):

QUARTERSTART    QUARTEREND
2013-09-01      2013-12-01
2013-06-01      2013-09-01
2013-03-01      2013-06-01

If the same query was run next month the result would be:

QUARTERSTART    QUARTEREND
2013-10-01      2014-01-01
2013-07-01      2013-10-01
2013-04-01      2013-07-01

You can then join your table data to this:

SELECT  DATE_ADD(DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 MONTH) ,'%Y-%m-01'), INTERVAL -n.Number - 1 QUARTER) AS QuarterStart,
        DATE_ADD(DATE_ADD(DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 MONTH) ,'%Y-%m-01'), INTERVAL -n.Number QUARTER), INTERVAL -1 DAY) AS QuarterEnd,
        COUNT(t.ID) AS `Count`
FROM    Numbers n
        INNER JOIN Table_Name t
            ON t.DateField >= DATE_ADD(DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 MONTH) ,'%Y-%m-01'), INTERVAL -n.Number - 1 QUARTER)
            AND t.DateField < DATE_ADD(DATE_FORMAT(DATE_ADD(NOW(), INTERVAL 1 MONTH) ,'%Y-%m-01'), INTERVAL -n.Number QUARTER)
GROUP BY n.Number;

Example on SQL Fiddle

N.B. In the select I have deducted one day from the quarter end field as visually the last day of the quarter appears more relevant, but for the join I have kept it as the first day of the next quarter, but used the less than operator

Of course if you also want to count empty quarters you can change the join to a LEFT JOIN, but you will probably then want to limit the number of rows you are returning by adding a Where clause.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.