Error on over() Clause for am calculate the rows based on id's to next or previous value in mysql

Question

I am facing an issue where my query is not executing and I am getting the error near the over clause but I want result like see the pic below:

I mean I want result like 25000-9000 =16000 ; 16000-5000 =11000; display output like that guys and here is my query

query:
SELECT s.id, s.cust_id, 
s.package_name,s.pending_amount,s.pack_received_amount, s.return_amount, 
s.payment_type,s.total_package_amount, SUM('s.pending_amount') OVER (ORDER 
BY s.id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW AS s.balance FROM 
payment s WHERE s.cust_id = S1307 and s.package_name= switzerland

My error was here screen shot of images guys:

Window functions have appeared in MySQL only after version 8.0.2 and above. You are using an old version of MySQL. — Madhur Bhaiya, Dec 14 '18 at 07:08
hi @MadhurBhaiya my version of MYSQL is 10.1.37-MariaDB but still its not working — Iswarya Swaminadhan, Dec 14 '18 at 07:15
@IswaryaSwaminadhan Window functions in Mariadb are available only in 10.3+ versions. You should upgrade to the latest version. — Madhur Bhaiya, Dec 14 '18 at 07:33
@IswaryaSwaminadhan you can check my approach. If you think it is helpful for you. — Jay Shankar Gupta, Dec 14 '18 at 07:57
hi @MadhurBhaiya my problem is sloved as according to your solution but when i upgrade to new mysql version am getting error before am not upgrade it okay "Field 'check_out' doesn't have a default value " it automatically place empty box in case it doesn't have . — Iswarya Swaminadhan, Dec 18 '18 at 05:43
@IswaryaSwaminadhan what is the datatype of `check_out` field ? Do `SHOW CREATE TABLE` and add the result of that to the question. — Madhur Bhaiya, Dec 18 '18 at 05:56
@MadhurBhaiya yeah i sloved the things using command '" SET GLOBAL sql_mode = ' '; " it doesn't shows now error anyways thank u so much buddy for ur help have a nice day. — Iswarya Swaminadhan, Dec 18 '18 at 06:24

score 0 · Answer 1 · answered Dec 14 '18 at 07:33

0

SELECT Total,
Received,
CASE WHEN @balance = 0 THEN
(@balance := Total - Received) 
ELSE 
@balance := @balance - Received
END AS Balance
FROM
Table1,
(select @balance:=0) as t2

Table1

Total   Received
-----------------
25000   9000
25000   5000

Output

Total   Received    Balance
----------------------------
25000   9000        16000
25000   5000        11000

DEMO

http://sqlfiddle.com/#!9/43cc31/18

answered Dec 14 '18 at 07:33

Jay Shankar Gupta

5,918
1
10
27

hi @Jay Shankar Gupta i want result like when i change one column remaining column balance automatically update the values but am not understand your query and thank u for answering and am almost try ur query also – Iswarya Swaminadhan Dec 14 '18 at 08:04
you can go for trigger, you can create a trigger on that table – Jay Shankar Gupta Dec 14 '18 at 08:06
these also a one method to solve the query thank u for answering – Iswarya Swaminadhan Dec 31 '18 at 05:43

score 0 · Accepted Answer · answered Dec 14 '18 at 12:09

This is your query:

select . . .,
       sum(s.pending_amount) over (order by s.id) as balance 
from payment s 
where s.cust_id = S1307 and s.package_name = 'switzerland';

If you don't have too many rows, a simple subquery will suffice:

select . . .,
       (select sum(s2.pending_amount)
        from payment s2
        where s2.id <= s.id and
              s2.cust_id = s.cust_id and
              s2.package_name = s.package_name
       ) as balance 
from payment s 
where s.cust_id = 'S1307' and
      s.package_name = 'switzerland';

Error on over() Clause for am calculate the rows based on id's to next or previous value in mysql

2 Answers2