vAmYqe6c

1`SELECT
2studentledger.ledgerno,
3SUM(studentledger.amount(ALL POSITIVE AMOUNT)) AS payables
4Sum(studentledger.amount(ALL NEGATIVE AMOUNT)) AS paymentsmade
5FROM
6studentledger
7WHERE
8studentledger.period =  '1'
9GROUP BY
10studentledger.ledgerno 
11     
12CREATE TABLE IF NOT EXISTS `studentledger` (
13  `ledgerno` int(11) NOT NULL 
14
15  AUTO_INCREMENT,
16  `sourcedoc` int(11) NOT NULL,
17  `student` int(11) NOT NULL,
18  `type` varchar(11) 
19
20NOT NULL,
21 `period` int(11) NOT NULL,
22 `amount` decimal(11,2) NOT NULL DEFAULT '0.00',
23 `date` 
24
25date NOT NULL,
26 PRIMARY KEY (ledgerno)
27) ENGINE=InnoDB  DEFAULT CHARSET=latin1 ` 
28     
29INSERT INTO `studentledger` 
30
31(`ledgerno`, `sourcedoc`, `student`, `type`, `period`, `amount`, `date`) 
32VALUES
33
34(3644, 144444, 164, 'A', 1, '18080.67', '2019-02-08'),
35(1462, 921020, 164, 'R', 1, '-5000.00', '2019-02-08'),
36(1465, 921265, 164, 'R', 1, '-5000.00', '2019-02-08'),
37(1467, 921592, 164, 'R', 1, '-3000.00', '2019-02-08'),
38(1212, 121125, 164, 'SA', 1, '42.00', '2019-02-08'),
39(6333, 916177, 164, 'R', 1, '-5122.67', '2019-02-12'),
40(1111, 920001, 152, 'A', 1, '18696.95', '2019-02-13'),
41(1023, 929258, 152, 'R', 1, '-2000.00', '2019-02-13'),
42(1133, 929267, 152, 'R', 1, '-3500.00', '2019-02-13'),
43(1211, 917588, 152, 'R', 1, '-500.00', '2019-02-13'),
44(1365, 932504, 152, 'SA', 1, '-96.00', '2019-02-13'),
45(1478, 920007, 152, 'R', 1, '-4000.00', '2019-02-13'),
46(1599, 922291, 152, 'R', 1, '-5000.00', '2019-02-13'),
47(1600, 932618, 152, 'R', 1, '-600.00', '2019-02-13'),
48(1743, 932752, 152, 'R', 1, '-2692.95', '2019-02-13'),
49(1630, 932618, 152, 'R', 1, '-400.00', '2019-02-13'),
50(1610, 932618, 152, 'R', 1, '-100.00', '2019-02-13'); 
51     
52`WHERE PAYABLES-PAYMENTSMADE != 0 //with remaining balance
53 OR
54  WHERE PAYABLES-PAYMENTSMADE < 0 //Overpayment`