CODEWITHSUNDEEP
mysqlsql
d1ch0t0my
d1ch0t0my

Reputation: 443

Group sql query into quarterly results

I'm trying to group a set of results into quarterly year results. So far I've managed to come up with this query which shows views by month: 

SELECT DATE_FORMAT(post_date, '%b-%y') AS Month, SUM(views) AS Views
FROM $table
WHERE `property_id` = $property_id
GROUP BY DATE_FORMAT(post_date, '%b-%y')
ORDER BY `post_date` ASC

...this gives me data such as the following:

Month   Views
Jan-16  331
Feb-16  390
Mar-16  431
Apr-16  430
May-16  364
Jun-16  360
Jul-16  459
Aug-16  356
Sep-16  530
Oct-16  435
Nov-16  376
Dec-16  362

I'm not sure how to group them so that the three months inclusive counts the views and outputs on that quarter end. For example, using the data above:

Month   Views
Mar-16  1152
Jun-16  1154
Sep-16  1345
Dec-16  1173

Any advice is appreciated, thanks.

Update Thanks to Thorsten Kettner for solving this. I also added in the following to his solution to get the column I needed in Month-Year format as follows:

SELECT DATE_FORMAT(MAX(post_date), '%b-%y') AS Month, QUARTER(post_date), SUM(views) AS Views
FROM $table
WHERE property_id = $property_id
GROUP BY YEAR(post_date), QUARTER(post_date) 
ORDER BY YEAR(post_date), QUARTER(post_date)

Upvotes: 1

Views: 611

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94959

The quarter can be calculated with an integer division from the month number: 

SELECT YEAR(post_date), (MONTH(post_date) - 1) DIV 3 + 1 as quarter, SUM(views) AS Views
FROM $table
WHERE property_id = $property_id
GROUP BY YEAR(post_date), (MONTH(post_date) - 1) DIV 3 + 1
ORDER BY YEAR(post_date), (MONTH(post_date) - 1) DIV 3 + 1;

As M Khalid Junaid says: there exists a function QUARTER for this:

SELECT YEAR(post_date), QUARTER(post_date), SUM(views) AS Views
FROM $table
WHERE property_id = $property_id
GROUP BY YEAR(post_date), QUARTER(post_date) 
ORDER BY YEAR(post_date), QUARTER(post_date);

Upvotes: 2

Related Questions