CODEWITHSUNDEEP
mysql
Haroon
Haroon

Reputation: 506

mysql: Group By using date_created in quartlerly report with DATE_FORMAT or quarter() functions

In my project, a monthly report is being generated for the revenue/sales from the client orders. Here is the sql fiddle for how my current report generation is working. Monthly report sqlfiddle: http://sqlfiddle.com/#!9/2ca03a/7

Query for monthly report:

SELECT SUM(price) as `Ordered total $`, date_created,
DATE_FORMAT(date_created, '%X - %b') as `Month`, COUNT(*) as `Total Orders` 
FROM orders WHERE class_id IN (1,2) 
GROUP BY DATE_FORMAT(date_created, '%X-%m');

Now I need to modify it to generate the report on quarter basis instead of monthly.

I have found i can use mysql quarter() function. I have tried using it but it did not work as intended. Here is what I have tried Quarter sqlfiddle: http://sqlfiddle.com/#!9/2ca03a/6

Query for quarter report which I have tried:

SELECT SUM(price) as `Ordered total $`, date_created,
 quarter(date_created) as `Quarter`,
 COUNT(*) as `Total Orders` 
 FROM orders WHERE class_id IN (1,2)
 GROUP BY
 quarter(date_created);

The mysql quarter() function is not getting records for quarters of all the years as you can see in my second sqlfiddle.

Is there any way to modify DATE_FORMAT() function to get quarter reports? Or How can I use quarter() function of mysql?

Upvotes: 0

Views: 110

Answers (1)

Nick
Nick

Reputation: 147196

You can use the YEAR() function to get the year from the date and CONCAT that to the quarter to give you a year/quarter value that you can group by:

SELECT SUM(price) as `Ordered total $`, date_created,
CONCAT(YEAR(date_created), ' - ', QUARTER(date_created)) as `Quarter`,
COUNT(*) as `Total Orders` 
FROM orders WHERE class_id IN (1,2)
GROUP BY `Quarter`

Updated SQLFiddle

Upvotes: 1

Related Questions