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 <[EMAIL PROTECTED]> Washington, DC
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
