Click here to Skip to main content
15,902,926 members
Please Sign up or sign in to vote.
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
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

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;
 
Share this answer
 

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900