-3

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:

enter image description here

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.

7
  • What code do you think you're generating with this? And how sure are you? Have you tested the code you think you're generating? Did it show the same result, and if so -- why aren't you just asking about that instead of PHP string concatenation? Nov 18, 2017 at 22:42
  • Please echo $sql; so we can discuss the SQL more easily.
    – Rick James
    Nov 19, 2017 at 0:39
  • @RickJames: Added the echo.
    – Robusto
    Nov 19, 2017 at 1:38
  • @EvanCarroll: The code works fine except for the LIMIT statement, which has no effect.
    – Robusto
    Nov 19, 2017 at 1:39
  • The expected effect of LIMIT whatever in the specific place in this query is exactly that, no effect. It would help if you tell us what the query is supposed to do. Then someone could offer a different query. Nov 19, 2017 at 2:09

2 Answers 2

2

Your subquery has an aggregate function (SUM) and no GROUP BY. This is an implicit GROUP BY and all the rows of the table are grouped into a single group / row. As a result the LIMIT has no effect there. You could have LIMIT 1 or LIMIT 5 or LIMIT 5000000 or no LIMIT at all. The result is the same (only LIMIT 0 would make a difference).

If you want:

  • for a specific month, the sum of mileage per rider, and then keep the top 5 riders according to this sum, The LIMIT should be applied in the main query or - since it is not needed - have no subquery at all:

    SELECT riderUid AS rider, 
           SUM(mileage) AS miles
    FROM mileage
    WHERE YEAR(rideDate) = YEAR(CURDATE()) 
      AND MONTH(rideDate) = MONTH(CURDATE()) 
    GROUP BY riderUid 
    ORDER BY miles DESC 
    LIMIT 5 ;
    

The date condition (current month) could also be rewritten, avoiding applying functions on the date/timestamp column so indexes can be used:

WHERE LAST_DAY(CURDATE()) + INTERVAL 1 DAY - INTERVAL 1 MONTH   -- first day of month
        <= rideDate 
  AND      rideDate 
             <  LAST_DAY(CURDATE()) + INTERVAL 1 DAY       -- first day of next month
1
  • This works beautifully, thanks. And thanks for being patient with a SQL noob.
    – Robusto
    Nov 19, 2017 at 14:09
1

What do you expect this to do

SELECT sum(v)
FROM ( SELECT 1 AS v UNION SELECT 2)
  AS t;

What about,

SELECT sum(v)
FROM ( SELECT 1 AS v UNION SELECT 2 )
  AS t
LIMIT 1;

Hint they're the same. LIMIT runs after the aggregate. Not sure where MySQL documents this, but PostgreSQL -- which has better documentation -- does so under SELECT.

5
  • Thanks, but when I try that I get no result at all.
    – Robusto
    Nov 19, 2017 at 2:01
  • What does that mean "no result at all? Nov 19, 2017 at 2:05
  • No rows returned. At all.
    – Robusto
    Nov 19, 2017 at 2:08
  • @Robusto lol, yea, ok.... may want to look at that. Nov 19, 2017 at 2:10
  • or, run your tests on dbfiddle or sqlfiddle. Nov 19, 2017 at 2:10

Not the answer you're looking for? Browse other questions tagged or ask your own question.