Reset Variable when row value is changed Below is an example of a SELECT statement. I have a variable @total that is set to 0, which works fine if I am doing a WHERE clause but I need the final total for each agent. Currently how this works is that each agent's records display and their total at the end. My question is how can I reset the variable when the agentID changes to the next agent and only display the final count.

select agentID, points,  
@total:= @total + points AS Total, comment 
from attendance, (select @total:= 0) v group by agentID, incurredDate


This is what I am trying to achieve.

=======================
agentID    |    total
=======================
brwilson   |      2
vimunson   |      5


http://sqlfiddle.com/#!2/d53222/8
select agentID,points,
case when (if(@prev_agent != agentID, @total := points, @total := @total + points)) is null then null
when (@prev_agent := agentID) is null then null
else @total end as running_total
, comment 
from attendance, (select @total:= 0.0, @prev_agent:=null) v 
order by agentID, incurredDate



see it working live in an sqlfiddle


In case you wonder, why I use this case when construct, please refer to this post.

P.S.: When using variables like this, you should have a order by clause. Group by in MySQL uses an implicit order by when none is specified. But remember using order by when you don't have a group by.

EDIT: Actually your group bydoesn't fit here, it seems to me. I changed it to a order by. And I changed your points column to datatype decimal(5,2), cause you have .5 in your sample data, which would become 1 when you continue to use int.

EDIT:

To get the total for each agent, just wrap above query in another query like this:

select agentID, MAX(running_total)
from (
    select agentID,points,
    case when (if(@prev_agent != agentID, @total := points, @total := @total + points)) is null then null
    when (@prev_agent := agentID) is null then null
    else @total end as running_total
    , comment 
    from attendance, (select @total:= 0.0, @prev_agent:=null) v 
    order by agentID, incurredDate
) sq
GROUP BY agentID



see it working live in an sqlfiddle