create a view in mysql query for finding balance of credit and debit and grouping by customer

Question

0.00/5 (No votes)

See more:

I am stuck with a query because i want to grouping the value "clv_cuota".
These are my data:

SQL

CREATE TABLE cuotas (
id_cuota int(11) NOT NULL,
clv_cuota int(11) DEFAULT NULL,
debe decimal(10,2) DEFAULT '0.00',
haber decimal(10,2) DEFAULT '0.00',
PRIMARY KEY (id_cuota)
) ENGINE=INNODB DEFAULT CHARSET=latin1;

CREATE VIEW Balances AS
SELECT m.`id_cuota`, m.`clv_cuota`, m.`debe`, m.`haber`,
(select sum(debe) - sum(haber) FROM cuotas m2
where m2.id_cuota <= m.id_cuota 
) as balance
FROM `cuotas` m
GROUP BY m.clv_cuota,  m.`id_cuota` DESC;

INSERT INTO `cuotas` (`id_cuota`, `clv_cuota`, `debe`, `haber`) VALUES
('18', '115', '100.00', '0.00'),
('19', '116', '100.00', '0.00'),
('20', '115', '50.00', '0.00'),
('21', '115', '0.00', '150.00'),
('23', '116', '50.00', '0.00'),
('24', '116', '20.00', '0.00');

the result of the query is:

id_cuota	clv_cuota	debe	haber	balance
21      	115	         0	    150	     100
20       	115	        50	     0	     250
18         	115	       100     	0	     100
24      	116     	20   	0	     170
23	        116	        50	     0	     150
19      	116	       100	     0	     200

It is grouped well but the result of the balance is not correct
I need a result like this

id_cuota	clv_cuota	debe	haber	balance
21      	115	         0	    150	       0
20       	115	        50	     0	     150
18         	115	       100     	0	     100
24      	116     	20   	0	     170
23	        116	        50	     0	     150
19      	116	       100	     0	     100

thanks in advance.

Posted 28-Dec-14 3:41am

Jose Garcia Sevilla

Add a Solution

Comments

Maciej Los 28-Dec-14 10:50am

Shouldn't be:

id_cuota	clv_cuota	debe	haber	balance
21      	115	         0	    150	     150
20       	115	        50	     0	     100
18         	115	       100     	0	       0
24      	116     	20   	0	     -20
23	        116	        50	     0	     -70
19      	116	       100	     0	     -170

Jose Garcia Sevilla 28-Dec-14 13:07pm

I think not, because the order is descending by id_cuota, and need it to be so.
for example, I managed to do it but with only one customer, clv_cuota=10

SELECT m.`id_cuota`, m.`clv_cuota`, m.`debe`, m.`haber`,
(select sum(debe) - sum(haber)
from cuotas m2
where m2.id_cuota <= m.id_cuota
AND m2.clv_cuota =10
) as balance
FROM `cuotas` m
WHERE m.clv_cuota =10
ORDER BY m.`id_cuota` DESC;

but I would like to see it with all customers not just one

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2014-12-28T06:40:00

Solution 1

Refer these:
Rolling Sums in SQL - A Practical Example[^]
Calculating a running balance to store with transactions[^]
Calculate a running total in MySQL[^]
How to calculate running balance of banking table using mysql [closed][^]
Calculate running balance in mysql select query and view[^]

Posted 28-Dec-14 6:40am

Maciej Los

Jose Garcia Sevilla · Accepted Answer · 2014-12-28T07:43:00

Solution 2

This is the query I wanted.
thx.

SQL

SELECT m.`id_cuota`, m.`clv_cuota`, m.`debe`, m.`haber`,
(select sum(debe) - sum(haber) FROM cuotas m2
where m2.id_cuota <= m.id_cuota and
    m2.clv_cuota = m.clv_cuota
) as balance
FROM `cuotas` m
GROUP BY m.clv_cuota,  m.`id_cuota` DESC;

Posted 28-Dec-14 7:43am

Jose Garcia Sevilla