CODEWITHSUNDEEP
pythonmysqldistinct
Bazz
Bazz

Reputation: 1

Using DISTINCT in MySQL

I would like a query that selects the total calories and Member ID from my table and order it by the total calories in descending order. I require the first 8 values to be printed out as ranking list, however the way I have designed my program is that every time a new record is added it calls the highest total calories value and then adds the new calculated value to it and stores this as the new total calories value.

This system creates a problem though as when I use this query:

SELECT TotalCalories, MemberID
FROM TrainingInfo
ORDER by TotalCalories desc
LIMIT 0,7

It wont fetch the correct amount of values in the case that the member may have two records which are both on the ranking list which I don't want, as I am using the SQL in python for my coursework, I can get rid of duplicate values but it wont give me 8 ranks. I have tried to using DISTINCT however I can't seem to get it to work, the MemberID will be the DISTINCT Value.

If someone could help me with the query that would be great.

Upvotes: 0

Views: 504

Answers (1)

mti2935
mti2935

Reputation: 12027

Are you saying that there could be multiple records for the same memberid? If so, then you would need to sum the totalcalories, and group by memberid:

select Memberid, sum(TotalCalories)
from TrainingInfo
group by MemberID
order by sum(TotalCalories) desc
limit 0,7

Upvotes: 1

Related Questions