SQL - problem selecting distinct data

[BeanoEFC]

Hey Everyone,

 

I am trying to display some data i have in my mysql database in a table.  im having a problem with the SQL to do it though.  what i am trying to do is display the amount of calories for a day, there could be multiple entries for a day.

 

This is the data from my database:

+-----------------------------+-----------------+
| substring(r.ride_date,1,10) | calories_burned |
+-----------------------------+-----------------+
| 2010-01-08                  |             250 | 
| 2010-01-08                  |             123 | 
| 2010-01-08                  |             123 | 
| 2010-01-10                  |             456 | 
+-----------------------------+-----------------+

What i want is to select the the distinct days (2010-01-08 and 2010-01-10) and add up the calories for them.

 

the SQL i am using is this:

select distinct(substring(r.ride_date,1,10)), sum(calories_burned)
from rides r 
join usersrides ur on r.ride_date = ur.ride_date 
join users u on ur.userid = u.userid 
where u.userid='1';

this does not produce the result im looking for though, it just sums up all the calories burned into one row

+-------------------------------+----------------------+
| (substring(r.ride_date,1,10)) | sum(calories_burned) |
+-------------------------------+----------------------+
| 2010-01-08                    |                  952 | 
+-------------------------------+----------------------+

 

Please can someone point me in the rite direction

Thanks in Advance,

-Beano

 

 

 

[PFMaBiSmAd]

Remove the distinct() term from the SELECT clause. Distinct is not a FUNCTION. It removes duplicate rows from the result set.

 

Add this after the WHERE clause -

 

GROUP BY DATE(r.ride_date)

[BeanoEFC]

Thank you PFMaBiSmAdPosted.

 

Regards,

-Beano