Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

1 Answer

up vote 1 down vote accepted
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

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
share|improve this answer
    
Please see this sqlfiddle sqlfiddle.com/#!2/db4a6/1 this is what I have been working with. Basically I need just the agentID and SUM of total points for each agent. There should only be 1 row for each agentID with the SUM total. Everything isn't important on the SELECT statement. –  Brandon Wilson Jan 11 at 21:31
    
@BrandonWilson Please have a look at my edited answer. Hope I understood correctly what you mean. –  tombom Jan 12 at 10:43
    
yes you understood it correctly and I figured it out late last night :) but now I'm running into a problem, the MAX was not always the MAX. sqlfiddle.com/#!2/02e2e/2 if you look at this example I added more data. As you can see with the agentID of deramsey it is suppose to be 11 because MAX is 11, for some reason it gives me 8.5. I was working on this for an hour last night before I gave up. –  Brandon Wilson Jan 12 at 15:22
    
I figured it out, there was a issue because MAX was thinking it was a STRING not a number. –  Brandon Wilson Jan 13 at 0:52
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.