-1

I'm sorry if this has been asked before, but I just can't find any answer that does the exact thing I need.

My cycling club logs mileage for every ride. To collect data per ride/per rider I have this table (together with a membership table I join to it):

CREATE TABLE `mileage` (
  `id` int(11) NOT NULL,
  `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'
) ENGINE=MyISAM DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

To output yearly data I use this MySQL query:

SELECT m.riderUid, w.firstName, w.lastName,
SUM(m.mileage) as miles,
COUNT(*) as cnt,
SUM(m.days) as days
FROM `mileage` m
    LEFT JOIN `members` w ON (w.uid = m.riderUid)
WHERE (m.rideDate >= '2018-01-01' AND m.rideDate < '2018-01-01')
GROUP BY m.riderUid
ORDER BY w.lastName;

This outputs totals by rider for the time period.

But I want to get the monthly totals as well, something like this:

Name         JAN | FEB | MAR | APR | MAY | JUN | JUL | AUG | SEP | OCT | NOV | DEC |
Rider 1      301 | 213 | 313 | 432 | 556 | 866 | 901 | 877 | 806 | 545 | 512 | 503 |
Rider 2      444 | 525 | 445 | 532 | 636 | 966 | 989 | 900 | 866 | 665 | 633 | 585 |
... etc.

Each row returned from the database will have either an array of monthly data or 12 named (or indexed) months for each row.

I feel like this ought to be simple, but so far it's not. I can easily crunch this in post-processing, but the processing time for that is too great.

Shadow
  • 33,525
  • 10
  • 51
  • 64
Robusto
  • 31,447
  • 8
  • 56
  • 77
  • 1
    This transformation is called pivot table and has been asked and answered here on SO soooo many times. Answers to the duplicate question show you how you can do this in mysql, however, contrary to your belief, it is usually lot more efficient to do such transformations in the application logic. – Shadow Oct 27 '19 at 00:23
  • @Shadow: Yeah, I know, I know, I'm not a noob to SE but I am also not a DBA, and if I'd known the term pivot table I'd have been able to go right to it. But the search terms I used kept producing what I already had, so I committed the sin you see here. Mea culpa. ^_^ – Robusto Oct 27 '19 at 01:36

1 Answers1

1

You can use conditional aggregation. For example, the following query gives you the total mileage par rider for each month in year 2018.

select 
    m.riderUid, 
    w.firstName, 
    w.lastName,
    sum(case when month(m.rideDate) = 1 then milage else 0 end ) jan,
    sum(case when month(m.rideDate) = 2 then milage else 0 end ) feb,
    sum(case when month(m.rideDate) = 3 then milage else 0 end ) mar,
    ...
    sum(case when month(m.rideDate) = 12 then milage else 0 end ) dec
from mileage m
left join members w on w.uid = m.rideruid
where m.ridedate >= '2018-01-01' and m.ridedate < '2019-01-01')
group by
    m.riderUid, 
    w.firstName, 
    w.lastName
order by w.lastname;
GMB
  • 216,147
  • 25
  • 84
  • 135
  • 1
    Why do we keep answering pivot table questions? It is asked on a daily basis. – Shadow Oct 27 '19 at 00:20
  • @Shadow: questions keep coming... maybe it is just a technique that is a little harder to acquire, even with so many well-documented answers already... – GMB Oct 27 '19 at 00:34
  • Thanks for taking the time, @GMB. This gave me what I needed better than the canonical question and my query works now. – Robusto Oct 27 '19 at 01:36