Query Problem

[californiasteve]

I have a running total that I am pulling from a database that I need to sort by year. I have tried adding WHERE YEAR(datepicker) = '2015' but keep getting an error. Here is the original query that works fine when I run it. Just don't need all the records.

 

SELECT id, datepicker, startmiles, endmiles, O.totalMiles, car, section, (select sum(totalMiles) FROM vehicle WHERE id <= O.id) 'runningTotal' FROM vehicle O ORDER BY datepicker DESC

 

Here is what I have tried.

 

SELECT id, datepicker, startmiles, endmiles, O.totalMiles, car, section, (select sum(totalMiles) FROM vehicle WHERE id <= O.id) 'runningTotal' FROM vehicle O AND YEAR(datepicker = '2015' ORDER BY datepicker DESC

Edited January 22, 2015 by californiasteve

[cyberRobot]

It looks like you're missing the close parenthesis for the YEAR() function here:

YEAR(datepicker = '2015'

If that doesn't fix the issue, what error(s) are you seeing?

[californiasteve]

I'm getting this error.

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AND YEAR(datepicker) = '2015'  
            ORDER BY datepicker DESC

using this query

 

SELECT id, datepicker, startmiles, endmiles, O.totalMiles, car, section, (select sum(totalMiles)
            FROM vehicle
            WHERE id <= O.id) 'runningTotal'
            FROM vehicle O
            AND YEAR(datepicker) = '2015'  
            ORDER BY datepicker DESC

[cyberRobot]

Try removing the "AND" before the YEAR() function.

AND YEAR(datepicker)

It also looks like you're missing the "WHERE" part in the where clause.

WHERE YEAR(datepicker) = '2015'

[Barand]

You are using a really inefficient dependent subquery which, for every record, queries the table to get the total miles for the vehicle. It would run quicker if you use a JOIN. EG

SELECT id
    , datepicker
    , startmiles
    , endmiles
    , O.totalMiles
    , car
    , section
    , runningTotal.totalMiles
FROM vehicle O
    INNER JOIN 
        (
        select id
        , sum(totalMiles) as totalMiles
        FROM vehicle
        GROUP BY id
        ) runningTotal USING (id)
WHERE YEAR(datepicker) = '2015'  
ORDER BY O.id, datepicker DESC

If, as the name suggests, you want a running total then use user variables in the query to accumulate the total

EG

SELECT id
    , datepicker
    , startmiles
    , endmiles
    , O.totalMiles
    , car
    , section
    , @rtot := IF(@id = id, @rtot+totalMiles, totalMiles) as runningTotal
    , @id := id as dummyId
FROM vehicle O
    JOIN (SELECT @id:+0, @rtot:=0) as init
WHERE YEAR(datepicker) = '2015'  
ORDER BY O.id, datepicker DESC