Question 407428 in Mysql Question posted in
The official MySQL documentation can be found here.

sum column value(s) if previous values has same value with current value from the same table in mysql

I’m trying to query sum(qtyin – qtyout) as newbalance based on the same price and the same productid.

Table – Stock

+----+------------+-------+--------+------+
| id |  ProductID | QtyIn | QtyOut |Price
+----+------------+-------+--------+------+
|  1 | 0100001    |  4    |     0  |1500  |
|  2 | 0100002    | 15    |     0  |2500  |
|  3 | 0100001    | 11    |     0  |1500  |
|  4 | 0100003    | 15    |     0  |3500  |
|  5 | 0100001    | 14    |     0  |2000  |
|  6 | 0100001    | 10    |     0  |1500  |
|  7 | 0100001    |       |    20  |1500  |
+----+------------+-------+--------+------+

I want the result to be like this

+----+------------+-------+--------+
| id |  ProductID |balance|Price
+----+------------+-------+--------+
|  1 | 0100001    |  5    |  1500  |
|  5 | 0100001    | 14    |  2000  |
|  2 | 0100002    | 15    |  2500  |
|  4 | 0100003    | 15    |  3500  |
+----+------------+-------+--------+

I have try like this

SELECT id,
       productid,
       (@prevQty := sum(QtyIn-QtyOut)) AS balance,
       (@prevPrice := Price) AS Price,
       (@prevQty := if(@prevPrice != Price, 0, sum(QtyIn-QtyOut))) AS newbalance
FROM stock AS s
GROUP BY productid,
         price
ORDER BY Productid,
         Id,
         Price;

but the result is not what I wanted

Tags:

2

Answers


  1. 0

    I don’t see what meaning ID has in the result, but how about:

    SELECT Productid,
       SUM(QtyIn - QtyOut) as balance,
       Price
FROM stock AS s
GROUP BY Productid,
         Price
ORDER BY Productid,
         Price;
    Login or Signup to reply.
  2. 0

    SELECT like_id, post_id
    FROM likes_table — assuming the table is named ‘likes_table’
    WHERE like_date < ‘2024-02-20’;

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search