Is it possible to output multidimensional data from linear table without iteration?

Question

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.

So, is this kind of thing possible using a query or do I have to brute-force it using PHP or JavaScript? I should add that I'm not a database guru by any stretch, just a journeyman coder.

Answer 1

I think you just need include the month in the GROUP BY:

SELECT m.riderUid, w.firstName, w.lastName, w.isMale,
 MONTH(m.rideDate) as monthNumber,
 SUM(mileage) as miles, 
 COUNT(*) as cnt 
FROM `mileage` m 
 INNER JOIN `members` w ON (w.uid = m.riderUid) 
WHERE (m.rideDate >= '$startDate' AND m.rideDate < '$endDate') 
GROUP BY m.riderUid, w.firstName, w.lastName, w.isMale, MONTH(m.rideDate) 
ORDER BY MONTH(m.rideDate), m.riderUid

If the date range is longer than a year, then months from different years will be folded together, so replace MONTH(m.rideDate) with LEFT(m.rideDate, 7) as suggested by @RickJames.

If you want the months as columns, then you need to "pivot" the months, see e.g. this answer on Stackoverflow. So that might give you a query with a subquery similar to the above, something like:

SELECT riderUID, firstName, lastName, isMale,
CASE WHEN monthNumber = 1 THEN miles END AS JanuaryMiles, 
CASE WHEN monthNumber = 1 THEN cnt END AS JanuaryCnt, 
CASE WHEN monthNumber = 2 THEN miles END AS FebruaryMiles, 
CASE WHEN monthNumber = 2 THEN cnt END AS FebruaryCnt,
CASE WHEN monthNumber = 3 THEN miles END AS MarchMiles, 
CASE WHEN monthNumber = 3 THEN cnt END AS MarchCnt,
CASE WHEN monthNumber = 4 THEN miles END AS AprilMiles, 
CASE WHEN monthNumber = 4 THEN cnt END AS AprilCnt,
CASE WHEN monthNumber = 5 THEN miles END AS MayMiles, 
CASE WHEN monthNumber = 5 THEN cnt END AS MayCnt,
CASE WHEN monthNumber = 6 THEN miles END AS JuneMiles, 
CASE WHEN monthNumber = 6 THEN cnt END AS JuneCnt,
CASE WHEN monthNumber = 7 THEN miles END AS JulyMiles, 
CASE WHEN monthNumber = 7 THEN cnt END AS JulyCnt,
CASE WHEN monthNumber = 8 THEN miles END AS AugustMiles, 
CASE WHEN monthNumber = 8 THEN cnt END AS AugustCnt,
CASE WHEN monthNumber = 9 THEN miles END AS SeptemberMiles, 
CASE WHEN monthNumber = 9 THEN cnt END AS SeptemberCnt,
CASE WHEN monthNumber = 10 THEN miles END AS OctoberMiles, 
CASE WHEN monthNumber = 10 THEN cnt END AS OctoberCnt,
CASE WHEN monthNumber = 11 THEN miles END NovemberMiles, 
CASE WHEN monthNumber = 11 THEN cnt END NovemberCnt, 
CASE WHEN monthNumber = 12 THEN miles END AS DecemberMiles, 
CASE WHEN monthNumber = 12 THEN cnt END AS DecemberCnt 
FROM (
 SELECT m.riderUid, w.firstName, w.lastName, w.isMale,
 MONTH(m.rideDate) as monthNumber,
 SUM(mileage) as miles, 
 COUNT(*) as cnt 
 FROM `mileage` m 
 INNER JOIN `members` w ON (w.uid = m.riderUid) 
 WHERE (m.rideDate >= '$startDate' AND m.rideDate < '$endDate') 
 GROUP BY m.riderUid, w.firstName, w.lastName, w.isMale, MONTH(m.rideDate) 
) q1
ORDER BY riderUid 

See here for db-fiddle example. Note: If running this query through phpMyAdmin, you may have to embrace each instance of CASE WHEN ... END in parentheses in order to workaround a bug in phpMyAdmin.

(I suspect the above can also be re-written more elegantly without the subquey using window functions, though that requires MySQL 8.0+ or MariaDB 10.2+.)

A couple of other tips:

• Include all the fields you SELECT in your GROUP BY - this is required by default in newer MySQL versions.
• Also, surely you can use INNER JOIN instead of OUTER JOIN in this case? Or do you have mileage for members who are no longer in the members table? If so, you may want to keep the LEFT JOIN.
• Also, InnoDB is a better choice than MyISAM in most use-cases now, and MyISAM is discouraged in MySQL 8.0+.

• Robusto: +1 Thanks for looking at this and the extra advice. Much appreciated. When I run this as is I get multiple entries for m.riderUid. What I need is to export CSV data with columns by month and rows as unique (grouped) m.riderUid names. I've tried with both INNER JOIN and LEFT JOIN, and the problem remains. Any thoughts?
• Rick James: If the date span is more than a year, MONTH() will fold things together. Do you want that? Instead this (or some other technique) will work: GROUP BY ..., LEFT(Date, 7)
• Robusto: @dbdemon: I prepended monthNumber to ORDER BY statement and I'm getting something interesting that could be split into month columns. I would like to keep the integrity of the rows across by m.riderUid, however. I suppose I would have to forego ordering by miles then, I guess.
• dbdemon: @Robusto - I've added a second query to the answer which shows the month totals per riderUid as columns. Hope that helps a bit!
• Robusto: @dbdemon: I found the culprit. Turns out if I put the CASE to END statements in parens I can run it: (CASE WHEN monthNumber = 1 THEN miles END) AS JanuaryMiles, There is apparently an unfixed bug in phpMyAdmin that makes this necessary. Thanks again for your help.