How to get subsequent total of column in next row using mysql select query

Question

I am creating one SQL query for stock entry. In the last column, I need the total of the previous stock purchase.

S No    Product Code    Qty     Qty Total
1       PO1             5       5  
2       PO1             12      17
3       PO1             10      27
4       PO1              8      35
5       PO1              9      44
6       PO1             16      60

In every row of the last column, I am adding all the previous quantity for example S no. 1 quantity is 5. In S No. 2 I am adding quantity 5 and 12=17. S No 3 I am adding 5 + 12+10=27 and Soo on.

I am sorry if it is a duplicate question. I search google and StackOverflow but didn't get the answer. I am new to MySQL I have added query below. I am new to SQL, any help appreciated,

Thanks in Advance.

CREATE TABLE `stock_table` (
  `ID` int(11) NOT NULL,
  `product_code` varchar(20) NOT NULL,
  `qty` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `stock_table` (`ID`, `product_code`, `qty`) VALUES
(1, 'PO1', 5),
(2, 'PO1', 12),
(3, 'PO1', 10),
(4, 'PO1', 8),
(5, 'PO1', 9),
(6, 'PO1', 16);

What is your MySQL server version ? Do `SELECT Version();` and report the results of this query into the question. — Madhur Bhaiya, Sep 03 '19 at 09:55
Possible duplicate of [Calculate a running total in MySQL](https://stackoverflow.com/questions/664700/calculate-a-running-total-in-mysql) — Madhur Bhaiya, Sep 03 '19 at 09:56
More answers available at: https://stackoverflow.com/q/2563918/2469308 and https://stackoverflow.com/q/19213633/2469308 and — Madhur Bhaiya, Sep 03 '19 at 09:56
I used select version: output is 10.1.34-MariaDB, I am using xamp — Gagandeep, Sep 03 '19 at 10:06

Ankit Bajpai · Answer 1 · 2019-09-03T10:31:34.633

1

You need a running sum. If you are using MySQL v 8.0, You can use SUM window function -

SELECT `ID`,
       `product_code`,
       `qty`,
       SUM(`qty`) OVER (PARTITION BY `product_code` ORDER BY `ID`)  Qty_Total
FROM `stock_table`;

fiddle

edited Sep 03 '19 at 10:31

answered Sep 03 '19 at 09:54

Ankit Bajpai

13,128
4
25
40

Thanks, Bro, It is working in MySQL 8, but I am using xamp and in it, it is not working on that.. Thank you very much for your answer. – Gagandeep Sep 03 '19 at 10:00
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(PARTITION BY `product_code` ORDER BY `ID`) FROM `stock_table` LIMIT 0, 25' at line 4 – Gagandeep Sep 03 '19 at 10:10
first it is saying unexpected token to Qty_Total – Gagandeep Sep 03 '19 at 10:11
1

It seems you are using MariaDB which doesn't support window function. Try Barbaros Özhan's answer. – Ankit Bajpai Sep 03 '19 at 10:12
when I removed the Qty_Total ,it is show ing above error #1064 – Gagandeep Sep 03 '19 at 10:12
@Gagandeep indeed, both answers are useful(+1), but windows analytic functions requires version 8+ as Ankit already mentioned. – Barbaros Özhan Sep 03 '19 at 10:14
Ok, Thanks Bro, His answer is awesome, still looking for more better option – Gagandeep Sep 03 '19 at 10:16
I am pretty sure, You can never found a better answer than him. – Ankit Bajpai Sep 03 '19 at 10:17

Barbaros Özhan · Answer 2 · 2019-09-03T12:13:44.373

You can use correlated subquery to sum up the desired column as the row proceeds by increasing ID value :

select t.*,
       ( select sum(`qty`) from `stock_table` where `ID` <= t.`ID` ) as "Qty Total"
  from `stock_table` t
 order by t.`ID`;

Demo

P.S. : as the sample data has unique product_code value, I didn't need to include the part related to product_code column's matching within the subquery, if it's the case, then consider converting your derived column to :

( select sum(`qty`) 
    from `stock_table` 
   where `ID` <= t.`ID` 
     and `product_code` = t.`product_code` ) as "Qty Total"

score 0 · Answer 3 · answered Sep 03 '19 at 11:02

Your version of MariaDB does not support window functions. That leaves you with two options. One is a correlated subquery; the second is variables.

The first is easier to implement:

select st.*,
       (select sum(st2.qty)
        from stock_table st2
        where st2.product_code = st.product_code and
              st2.id <= st.id
       ) as running_qty
from stock_table st;

For performance, you want an index on stock_table(product_code, id, qty).

How to get subsequent total of column in next row using mysql select query

3 Answers3