Question 406504 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to turn everything from a per-year basis to a per-school-year basis?

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?

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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:
    enter image description here

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search