CODEWITHSUNDEEP
phpmysqlcumulative-sum
Craig Howell
Craig Howell

Reputation: 1184

Mysql Running Total off one table

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);

  }

Upvotes: 1

Views: 48

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521073

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:

enter image description here

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.

Upvotes: 3

Related Questions