I have the following table on a cycling club's website:
CREATE TABLE `mileage` ( `id` int(11) NOT NULL AUTO_INCREMENT, `rideID` mediumint(9) NOT NULL, `rideDate` date NOT NULL, `isWeekly` tinyint(1) NOT NULL, `riderUid` smallint(6) NOT NULL, `leaderUid` smallint(6) NOT NULL, `mileage` smallint(6) NOT NULL, `days` smallint(6) NOT NULL DEFAULT '1', `eBike` tinyint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`id`) ) ENGINE=MyISAM AUTO_INCREMENT=8081 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
Each entry represents one rider/trip, where the rider is the person who rode and the trip is the rider's part of a club ride.
When the site was built, the club wanted the ability to get mileage between dates (defaulting to current month or YTD).
The following query works well for that application (variables are from PHP):
SELECT m.riderUid, w.firstName, w.lastName, w.isMale, SUM(mileage) as miles, COUNT(*) as cnt FROM `mileage` m LEFT JOIN `members` w ON (w.uid = m.riderUid) WHERE (m.rideDate >= '$startDate' AND m.rideDate < '$endDate') GROUP BY m.riderUid ORDER BY miles DESC
Now they've requested to get the data month-by-month for any given pair of dates. Meaning they want to see columns output for, say, January, February, March ... etc.
I suppose I can do this iteratively, using PHP to feed successive monthly start and end dates and then glomming them all together on the page, but that feels kind of clunky. I wonder if there's an easier way using a MySQL query.