We have a lot of data with a lot of events with a lot of dates in a MySQL db. Generating lists of e.g. events per year, or students per year, is fairly simple. Now, we’ve decided to change everything to “school year”, that is e.g. the school year 2022-23 or 2024-25. For example, we’re currently doing something like
SELECT COUNT(*) FROM events GROUP BY YEAR(startdate)
I can hardly imagine how we would do this in a simple manner, if all years now run from July 1st to June 30th.
How in the world would I do something like this in MySQL?
1
You can still use the YEAR()-function by adding 7 months to the date:
SELECT concat(YEAR(startdate +interval +7 month)-1,'-',SUBSTR(YEAR(startdate +interval +7 month),-2)) as schoolyear,
COUNT(*)
FROM events
GROUP BY schoolyear;
If you use that in many queries, you can make a function out of that and use it:
create function f_schoolyear( in_date date )
returns char(7)
deterministic
begin
return concat(YEAR(in_date +interval +7 month)-1,'-',SUBSTR(YEAR(in_date +interval +7 month),-2));
end
Usage:
SELECT f_schoolyear(startdate) as schoolyear, COUNT(*)
FROM events
GROUP BY schoolyear;
See a dbfiddle.
So, school session is: Current year July 1st to Next year June 30th
i.e Current year Month-07 to Next year Month-6.
First find session and use count on session as follows:
select
count(*) as total_students,
sc_session
from (
select
*,
CASE
WHEN (month(startdate) > 6)
THEN CONCAT(CAST(year(startdate) AS CHAR(4)), '-', CAST(year(startdate)+1 AS CHAR(4)))
ELSE CONCAT(CAST(year(startdate)-1 AS CHAR(4)), '-', CAST(year(startdate) AS CHAR(4)))
END AS sc_session
from events
) t
group by sc_session;
working perfectly with my local db: