MySQL Variables – Reset Variable When Row Value Changes

MySQL

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

Best Answer

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

Related Solutions

Mysql – Optimization of complicated sums and counts in related tables

Your question shows you are thinking in a procedural way, however you need to think in a "set-based" way.

The following query may help you understand this better:

SELECT p.customer_id
    , c.name
    , sum(t.points)
FROM products p
    INNER JOIN types t ON p.type_id = t.id
    INNER JOIN categories c ON t.category_id = c.id
GROUP BY p.customer_id
    , c.name;

Essentially, this should show you a list of customer_id, category name, and the total points each customer has in each category. It accomplishes this through the use of an aggregate function (SUM) along with the GROUP BY clause.

(please take a look at my answer here for information about naming your ID columns)

MySQL – Multi-Table Sum of Cost Breakdown by Pricing Category

Unfortunately, MySQL is lacking window or analytic functions which make complex grouping queries easier. For your use case, though, it should be possible to get it working with a single query.

Doing a simple row-based group of your sample dataset:

MySQL [test]> select orderId , category, count(*), sum(subtotal) from OrderLineItems group by 1,2;
+---------+-----------+----------+---------------+
| orderId | category  | count(*) | sum(subtotal) |
+---------+-----------+----------+---------------+
|  323607 | BUILD     |        5 |       2515.00 |
|  323607 | FINISHING |        1 |         30.00 |
|  323607 | SETUP     |        2 |         95.00 |
|  323614 | BUILD     |        4 |       4614.00 |
|  323614 | SETUP     |        1 |         36.00 |
|  323636 | BUILD     |        2 |        840.00 |
|  323636 | FINISHING |        1 |        130.00 |
|  323636 | SETUP     |        7 |       1152.00 |
|  323649 | BUILD     |        1 |        240.00 |
|  323649 | FINISHING |        1 |         30.00 |
|  323649 | SETUP     |        4 |       1995.00 |
+---------+-----------+----------+---------------+
11 rows in set (0.00 sec)

You can pivot the subtotals to columns using the trick of using an IF statement in the select. This way you get the main part of your data with only one query:

SELECT orderId , count(*), 
sum(if(category='BUILD', subtotal, 0)) as Build_Total,
sum(if(category='SETUP', subtotal, 0)) as Setup_Total,
sum(if(category='FINISHING', subtotal, 0)) as Finishing_Total,
sum(subtotal) as Total
from OrderLineItems
GROUP BY 1;

With output:

+---------+----------+-------------+-------------+-----------------+---------+
| orderId | count(*) | Build_Total | Setup_Total | Finishing_Total | Total   |
+---------+----------+-------------+-------------+-----------------+---------+
|  323607 |        8 |     2515.00 |       95.00 |           30.00 | 2640.00 |
|  323614 |        5 |     4614.00 |       36.00 |            0.00 | 4650.00 |
|  323636 |       10 |      840.00 |     1152.00 |          130.00 | 2122.00 |
|  323649 |        6 |      240.00 |     1995.00 |           30.00 | 2265.00 |
+---------+----------+-------------+-------------+-----------------+---------+
4 rows in set (0.00 sec)

You can use the same approach to get your date-based rollups.

Best Answer

Related Solutions

Mysql – Optimization of complicated sums and counts in related tables

MySQL – Multi-Table Sum of Cost Breakdown by Pricing Category

Related Question