Mauricio Pellegrini wrote:
>and would like to obtain this result from a query
>
> Col1 Col2 Col3
> 1 20 20
> 1 10 30
> 1 20 50
> 2 10 10
> 2 5 15
> 3 10 10
>
>Column Col3 should carry forward and sum values from Col2
>
Something like this should work, using two variables, @total and @prev:
SELECT Col1, Col2, @total := IF(@prev = Col1, @total + Col2, Col2 +
(@prev := Col1) - Col1)
FROM table_name
ORDER BY Col1;
The way I'm setting @prev every time Col1 changes is a bit klugy (having
to add it in and
then subtract Col1 to fix it), but it seems to work.
Hmm, if you change the order of the result columns you can avoid the kluge:
SELECT Col2, @total := IF(@prev = Col1, @total + Col2, Col2), @prev
:= Col1
FROM table_name
ORDER BY Col1;
--
Keith Ivey <keith@stripped>
Washington, DC
