CODEWITHSUNDEEP
mysqlsqlgroup-bysum
Andrey
Andrey

Reputation: 1496

Subtract a value from previous row respecting the grouping

I have the following table:

CREATE TABLE `movm` (
    `id` INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `product` INTEGER NOT NULL,
    `value` DECIMAL(10, 2) NOT NULL
    -- ...
);

And a couple of data:

INSERT INTO `movm`(`product`, `value`) VALUES 
    (1, 1000),
    (1, -200),
    (1,  100),
    (2,   50),
    (2,  -10),
    (1,  100),
    (2,  -20);

And I'd expect to get the following result:

+----+---------+---------+----------+---------+
| id | product | value   | previous | current |
+----+---------+---------+----------+---------+
|  1 |       1 | 1000.00 |        0 |    1000 |
|  2 |       1 | -200.00 |     1000 |     800 |
|  3 |       1 |  100.00 |      800 |     900 |
|  6 |       1 |  100.00 |      900 |    1000 |
|  4 |       2 |   50.00 |        0 |      50 |
|  5 |       2 |  -10.00 |       50 |      40 |
|  7 |       2 |  -20.00 |       40 |      20 |

But always respecting a GROUP BY product.

I've tried the following SQL:

SELECT
    `id`,
    `product`,
    `value`,
    @previous := (@current)           AS `previous`,
    @current  := (@current + `value`) AS `current`
    FROM
        `movm`,
        (
            SELECT
                @previous := 0,
                @current  := 0
        ) AS `__movm`;

Because it doesn't respect the group by, it always sums everything, and there's no tiebreaker.

Already did IF(@product != product, @current := 0, NULL); works, but not so much flexible. And I also need to track only the values for that product, bypassing any order, as you see, they're not sorted (I'd think that this is easy to solve wrapping current the SQL in a sub-query).

So: is there a better solution which I can use GROUP BY product and get the expected result as shown?

Upvotes: 1

Views: 1427

Answers (1)

radar
radar

Reputation: 13425

you need to have another variable to keep track of the product and also you need order by so that you can change the product variable and reset counters when product value changes.

This will work.

SELECT
    id,
    product,
    value,
    @previous := IF( @prevProduct = product,@current,0)           AS previous,
    @current  := IF( @prevProduct = product, @current + value, value) AS current,
    @prevProduct := product as var_val 
    FROM
    movm,
        (
            SELECT
                @previous := 0,
                @current  := 0,
                @prevProduct := NULL
        ) AS `__movm`
    ORDER BY product

Upvotes: 2

Related Questions