Need help creating a view that contains two different sums

Question

I have a view that's currently defined as

CREATE VIEW riders AS select ridelog.uid AS rid,sum(ridelog.distance) AS distance from ridelog group by ridelog.uid

What I'd like to do is add a second field that shows the distance y-t-d. i.e, the sum of the distances for rides that occurred since January 1st of the current year.

The problem is, I can't figure out how to say "current year" in mysql or if I can create a view that has sums with different criteria in them.

Is this possible?

Answer 1

This will do it - as long as your view should always contain a column for the current year.

CREATE VIEW riders AS 
    SELECT 
        ridelog.uid AS rid,
        SUM(ridelog.distance) AS distance,
        SUM(IF(YEAR(ridelog.<<date_time_column>>) = YEAR(NOW()), ridelog.distance, 0) AS distanceThisYear 
    FROM ridelog 
    GROUP BY ridelog.uid

With this approach you cannot specify the year for which you want the distance calculated. Another possibility would be to use a stored procedure with the year as its parameter.