MySQL – Output Multidimensional Data from Linear Table Without Iteration

MySQLPHP

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.

Best Answer

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+.
Related Question