I commented out the LIMIT statement because it’s not working. All selected rows are always returned, no matter what.
Code:
$ sql = "SELECT riderUid AS `rider`, ( "; $ sql .= "SELECT SUM( mileage ) "; $ sql .= "FROM `mileage` "; if (isset($ ytd) && $ ytd == 1) { $ sql .= "WHERE riderUid = `rider` AND $ year = YEAR(CURDATE()) "; $ title = "Year-to-date"; } else { $ sql .= "WHERE riderUid = `rider` AND YEAR(rideDate) = YEAR(CURDATE()) AND MONTH(rideDate) = MONTH(CURDATE()) "; $ title = "This month"; } $ sql .= "ORDER BY mileage DESC "; //$ sql .= "LIMIT 5 "; $ sql .= ") AS `miles` "; $ sql .= "FROM `mileage` "; $ sql .= "GROUP BY riderUid "; $ sql .= "ORDER BY `miles` DESC";
I tried the statement in a couple other locations, without success.
result of echo $ sql;
with the LIMIT statement back in:
SELECT riderUid AS `rider`, ( SELECT SUM( mileage ) FROM `mileage` WHERE riderUid = `rider` AND YEAR(rideDate) = YEAR(CURDATE()) AND MONTH(rideDate) = MONTH(CURDATE()) ORDER BY mileage DESC LIMIT 5) AS `miles` FROM `mileage` GROUP BY riderUid ORDER BY `miles` DESC
Addendum
Here’s the table structure:
Each row represents one rider/ride (riderUid/rideID).
What I want to do is to sum each rider’s mileage then return an ordered set of rows from most to least miles per rider (monthly or YTD).
A version of this would limit it to just the top n
riders, which would be for a portable widget (one that could go on any page).
Currently I iterate through the rows and get the usernames, etc. from other tables. Ideally I’d like to do a JOIN in this query to get those, but I’m not a DBA and not skilled enough to do that. What I have here I harvested from SO, with a few modifications, and if anybody can suggest a better way to do it I’d be most grateful.