[RESOLVED] Group by SCHOOL YEAR?

Joseph_Sliker

Hi;

I work for a school system and I am wondering if anyone knows any MySQL query tricks that will allow queries to group by non-standard date groupings (without too much extra code). I have a lot of data tables with date fields and I would often like to do aggregate/group queries by school year (ie. starting (YEAR)-09-01 through (YEAR+1)-08-31).

So, given a data table with zillions of records and with fields like...

+------------+--------+----------+-------+------+------------+
| PROTOCOLID | TEST | SCHOOLID | GRADE | ROOM | TESTDATE |....
+------------+--------+----------+-------+------+------------+
| 423 | MATH | 185 | 1 | 0210 | 2006-05-17 |....
| 200 | ORF | 179 | 1 | 0016 | 2007-12-11 |....
| 201 | ORF | 179 | 1 | 0016 | 2005-04-11 |....
| 202 | IGDIS | 179 | 1 | 0016 | 2007-09-11 |....
| 203 | DIB | 179 | 1 | 0016 | 2004-03-11 |....
.....

a query could be built to output like:

+-----------+----+
| YEAR | N |
+-----------+----+
| 2001-2002 | 1 |
| 2002-2003 | 29 |
| 2003-2004 | 48 |
| 2004-2005 | 59 |
| 2005-2006 | 61 |
| 2006-2007 | 71 |
| 2007-2008 | 98 |
+-----------+----+

I am already using a dummy variable (a letter code for the school year) that I can use for such purposes, but I did not use it consistently in all my data tables, but I'd actually like to stop using that and rely on less kludgy programming.

...that, and I am always trying to live up to my philosphy of "The Lazy Man Will Find The Best Way."

It would be really slick if anyone could point out some MySQL function that would do such a thing "on the fly" rather than having to, say, loop through multiple queries with an array of start and end date filters.

Any ideas?

Thanks!

Sxooter

OK, in every db, there's a function that can pull the year out of a date / timestamp. For instance, with pgsql, you can do:

extract(year from datefield);

so you could group by that:

select avg(a), min(b), extract(year from datefield) group by extract(year from datefield);

Joseph_Sliker

That's not quite the issue...a school year typically starts in say, September so one would want to group data on Sept of year through, say the end of June year+1.

I've gotten part of the way with this....

SELECT TESTDATE, LEFT(TESTDATE, 4) AS YEAR, (IF(SUBSTR(TESTDATE, 6, 2)<'09',
-> ((LEFT(TESTDATE, 4)-1)),
-> ((LEFT(TESTDATE, 4)))
-> )) AS SCHOOLYEAR FROM DATA_PROTOCOLS WHERE TESTDATE !='0000-00-00' LIMIT 10;
+------------+------+------------+
| TESTDATE | YEAR | SCHOOLYEAR |
+------------+------+------------+
| 2000-05-09 | 2000 | 1999 |
| 2000-09-28 | 2000 | 2000 |
| 2001-10-21 | 2001 | 2001 |
| 2001-10-23 | 2001 | 2001 |
| 2001-10-23 | 2001 | 2001 |
| 2001-10-23 | 2001 | 2001 |
| 2001-10-23 | 2001 | 2001 |
| 2001-10-23 | 2001 | 2001 |
| 2001-10-23 | 2001 | 2001 |
| 2001-10-23 | 2001 | 2001 |
+------------+------+------------+
10 rows in set (0.00 sec)

...but I would like to take it the rest of the way and make the school years read say 2001-2002, 2002-2003, etc.

Joseph_Sliker

Okay. Now I think I've got it!

SELECT TESTDATE, LEFT(TESTDATE, 4) AS YEAR, (IF(SUBSTR(TESTDATE, 6, 2)<'09',CONCAT((LEFT(TESTDATE, 4)-1),'-',(LEFT(TESTDATE, 4))),CONCAT((LEFT(TESTDATE, 4)),'-',(LEFT(TESTDATE, 4)+1)))) AS SCHOOLYEAR FROM DATA_PROTOCOLS WHERE TESTDATE !='0000-00-00' LIMIT 10;

+------------+------+------------+
| TESTDATE | YEAR | SCHOOLYEAR |
+------------+------+------------+
| 2000-05-09 | 2000 | 1999-2000 |
| 2000-09-28 | 2000 | 2000-2001 |
| 2001-10-21 | 2001 | 2001-2002 |
| 2001-10-23 | 2001 | 2001-2002 |
| 2001-10-23 | 2001 | 2001-2002 |
| 2001-10-23 | 2001 | 2001-2002 |
| 2001-10-23 | 2001 | 2001-2002 |
| 2001-10-23 | 2001 | 2001-2002 |
| 2001-10-23 | 2001 | 2001-2002 |
| 2001-10-23 | 2001 | 2001-2002 |
+------------+------+------------+
10 rows in set (0.00 sec)

Now I've gotta figure out to deal with the data from those )&&(^%$#W&)(& year-round school programs.