Mysql Running Total off one table

Question

I have looked up a few different answers to this question and can't seem to get the query to work properly.

Here is my table has the columns user, weekNo, salesTotalYTD.

I am currently pulling these out and grouping them by week like so:

+------+--------+---------------+
| user | weekNo | salesTotalYTD |
+------+--------+---------------+
|Jared |   1    |      200      |
+------+--------+---------------+
|  Jim |   1    |      50       |
+------+--------+---------------+
|Jared |   2    |      30       |
+------+--------+---------------+
| Jim  |   2    |      100      |
+------+--------+---------------+

What I am trying to do but cannot accomplish is the following:

+------+--------+---------------+
| user | weekNo | salesTotalYTD |
+------+--------+---------------+
|Jared |   1    |      200      |
+------+--------+---------------+
| Jim  |   1    |      50       |
+------+--------+---------------+
|Jared |   2    |      230      |
+------+--------+---------------+
| Jim  |   2    |      150      |
+------+--------+---------------+

This is the query that I have working for the first pass but every pass after that is wrong:

SET @runtot:=0

SELECT
    salesTotalYTD,
    user,
    (@runtot := @runtot + salesTotalYTD) AS rt
  FROM weeksAndSalesmantbl
  GROUP BY user, weekNo
  ORDER BY (CASE WHEN weekNo = 52 THEN 0 ELSE 1 END) ASC, weekNo, user ASC

Updated

Updated code courtesy of Tim but returning error:

$assignments = "
    SELECT
    t1.user,
    t1.weekNo,
    (SELECT SUM(t2.salesTotalYTD) FROM weeksAndSalesmantbl t2
    WHERE t2.user = t1.user AND t2.weekNo <= t1.weekNo) AS salesTotalYTD
    FROM weeksAndSalesmantbl t1
    ORDER BY
    t1.weekNo,
    t1.user";

  $salesTotalSalesManCumulative = [];

  $assignmentsqry = mysqli_query($db,$assignments);

  if (!$assignmentsqry) {
     printf("Error: %s\n", mysqli_error($db));
     exit();
  }

  while ($row = mysqli_fetch_array($assignmentsqry)) {

    $float = floatval($row['salesTotalYTD']);
    $float = round($float,2);

    array_push($salesTotalSalesManCumulative,$float);

  }

I have upvoted your question. We need a PHP person to look at this and render an opinion. I think your original approach with session variables is also good. — Tim Biegeleisen, Jul 27 '17 at 13:55
Have a look [here](https://stackoverflow.com/questions/15439919/mysqli-fetch-array-expects-parameter-1-to-be-mysqli-result-boolean-given-in). I think the query is failing, not sure why. — Tim Biegeleisen, Jul 27 '17 at 14:00
@TimBiegeleisen I utilized the error handling with the link you provided and the following error was printed: `Error: Can't reopen table: 't1'` I will add the error handling in the question. — Craig Howell, Jul 27 '17 at 14:02
[Read here](https://dev.mysql.com/doc/refman/5.7/en/temporary-table-problems.html). PHP doesn't like us referring to the same table in the subquery as it also appears in the outer query. I'm surprised by this, because this query would certainly run if called from most other languages. — Tim Biegeleisen, Jul 27 '17 at 14:05
OK...let me try to get your original query using session variables...5 minutes please. — Tim Biegeleisen, Jul 27 '17 at 14:07
I did fail to mention this is a temporary table which explains why the error is occurring based on the link provided by @TimBiegeleisen — Craig Howell, Jul 27 '17 at 14:09

Tim Biegeleisen · Accepted Answer · 2017-07-27T14:14:19.630

3

You can approach this using the standard running total query. However, in this case, we also restrict the sum to a particular user.

SELECT
    t1.user,
    t1.weekNo,
    (SELECT SUM(t2.salesTotalYTD) FROM weeksAndSalesmantbl t2
     WHERE t2.user = t1.user AND t2.weekNo <= t1.weekNo) AS salesTotalYTD
FROM weeksAndSalesmantbl t1
ORDER BY
    t1.weekNo,
    t1.user

Output:

Demo here:

Rextester

Update:

Since late in the game you told us that weeksAndSalesmantbl is a temporary table, and MySQL does not like the query I gave above, we can consider using a single pass over your table with session variables.

SET @rt = NULL;
SET @user = NULL;

SELECT
    t.user,
    t.weekNo,
    t.rt AS salesTotalYTD
FROM
(
    SELECT
        @rt:=CASE WHEN @user=user THEN @rt+salesTotalYTD ELSE salesTotalYTD END AS rt,
        @user:=user AS user,
        weekNo
    FROM weeksAndSalesmantbl
    ORDER BY
        user,
        weekNo
) t
ORDER BY
    t.weekNo,
    t.user;

Demo

If this still gives you the error, then you might want to think about getting rid of that temporary table. Anyway, you probably would not want to be using a temporary table in production.

edited Jul 27 '17 at 14:14

answered Jul 27 '17 at 13:39

Tim Biegeleisen

502,043
27
286
360

I received the following error: `Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result` – Craig Howell Jul 27 '17 at 13:42
@CraigHowell My query works, q.v. the demo. If you have another problem, maybe you are doing some incorrect in your PHP code. – Tim Biegeleisen Jul 27 '17 at 13:44
I will post my full code with PHP. It was running properly prior to swapping out with your code. – Craig Howell Jul 27 '17 at 13:49
The second approach has fixed the error but instead of returning a cumulative value for each week I am getting a result that is unexpected. I am going to try a few things and get back to you. Thanks @TimBiegeleisen – Craig Howell Jul 27 '17 at 14:32
for some reason I am getting twice as many rows/results as I should. I can see with your link that the MYSQL is working as expected but for some reason, it is not returning correctly in my code. It is very strange and I can't put my finger on why it is doing this. – Craig Howell Jul 27 '17 at 15:59
I used your direction at the bottom to stop utilizing temporary tables in production and utilized your first query example along with setting `user, weekNo` as PRIMARY KEY and it works like a charm. Thanks! – Craig Howell Jul 27 '17 at 17:20

Mysql Running Total off one table

1 Answers1

Rextester

Demo