MySQL Forums
Forum List  »  Newbie

Re: SQL select statement (both SUM and not)
Posted by: Roland Bouman
Date: August 21, 2005 05:02PM

Hi,

select curr.id
, curr.dude_id
, curr.round
, curr.points
, sum(prev.points) cumulative_points
from rounds curr
inner join rounds prev
on curr.dude_id = prev.dude_id
and curr.round >= prev.round
group by curr.id
, curr.dude_id
, curr.round
, curr.points
having curr.round = max(prev.round)


will give you the number of points per dude per round, along with the total number of points the dude earned when we count all rounds so far. However, this returns this data for all rounds. I didnt gather from your post if you do need the info for all rounds, or just for the last round. If that is the case, you still need to filter for the maximum round per dude. You can achieve this by writing this:

select curr.id
, curr.dude_id
, curr.round
, curr.points
, sum(prev.points)
from rounds curr
inner join rounds prev
on curr.dude_id = prev.dude_id
and curr.round >= prev.round
left join rounds next
on curr.dude_id = next.dude_id
and curr.round < next.round
where next.round is null
group by curr.id
, curr.dude_id
, curr.round
, curr.points
;

or like this:

select curr.id
, curr.dude_id
, curr.round
, curr.points
, sum(prev.points)
from rounds curr
inner join rounds prev
on curr.dude_id = prev.dude_id
and curr.round >= prev.round
where curr.round = (
select max(round)
from rounds m
where m.dude_id = m.dude_id
)
group by curr.id
, curr.dude_id
, curr.round
, curr.points
;

whatever you like best.

Options: ReplyQuote


Subject
Written By
Posted
Re: SQL select statement (both SUM and not)
August 21, 2005 05:02PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.