SQL query Group records by given Date Range

Question

I have a table which have some field there is a checkup_date field(type=datetime, ex: value= '2013-11-13 14:20:30'). I write a query group by year

SELECT year(eh_patient_checkup.checkup_date) as 'Year', count(*)
  FROM phcdb.eh_patient_checkup eh_patient_checkup
GROUP BY year(eh_patient_checkup.checkup_date)

it show me result like

Year ----- count(*)
-----------------
2011 ----- 203
2012 ----- 1007
2013 ----- 3020

But I want it to GROUP BY every April 1 to March 31(next year). For example

Year ------------------------- count(*)
--------------------------------------
2011-04-01 to 2012-03-31 ----- 203
2012-04-01 to 2013-03-31 ----- 5656
2013-04-01 to 2014-03-31 ----- 1213

Can anyone help me for improving my query?

Michael Mairegger · Accepted Answer · 2013-11-13 10:08:28Z

1

You can use datediff

SELECT date_add('2013-04-01', INTERVAL YearDiff YEAR) 'from date'
, date_add('2013-04-01', INTERVAL YearDiff + 1 YEAR) 'to date'
, Amount
FROM
(
    SELECT floor(datediff(eh_patient_checkup.checkup_date,'2013-04-01') / 365) as 'YearDiff', count(*) AS 'Amount'
    FROM phcdb.eh_patient_checkup eh_patient_checkup
    GROUP BY floor(datediff(eh_patient_checkup.checkup_date,'2013-04-01') / 365)
) as temp

The only work to do is that you have to get the correct year-range in the select.

edited Nov 13, 2013 at 10:08

answered Nov 13, 2013 at 9:39

Michael Mairegger

7,35133 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Zahidul Hossein Ripon Over a year ago

MySQL Database Error: Incorrect parameter count in the call to native function 'datediff'

Michael Mairegger Over a year ago

May be because of the double quotes in the date. I have removed it.

Michael Mairegger Over a year ago

Sorry, I already saw, that you use mysql, my query is for mssql. I will change it

Michael Mairegger Over a year ago

yea, thats the year-diff. Now we have to apply the date_add function.

Zahidul Hossein Ripon Over a year ago

MySQL Database Error: Every derived table must have its own alias

Collectives™ on Stack Overflow

SQL query Group records by given Date Range

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related