Re: SQL select statement (both SUM and not)
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.