matinict matinict - 8 days ago 8
MySQL Question

How Can Calculate Credits,Debits & Balance in the general ledger?

Balance Not showing proper value

MySQL Database Table:

CREATE TABLE `transactions` (
`trx_id` int(11) NOT NULL AUTO_INCREMENT,
`trx_type` enum('debit','credit') DEFAULT NULL,
`trx_amount` float DEFAULT NULL,
`trx_description` mediumtext NOT NULL,
`trx_date` date NOT NULL,
`purpose_id` int(11) NOT NULL,
`staff_id` int(11) DEFAULT NULL,
`admin_id` int(11) NOT NULL,
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`trx_id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;


/*Data for the table
transactions
*/

insert into `transactions`(`trx_id`,`trx_type`,`trx_amount`,`trx_description`,`trx_date`,`purpose_id`,`staff_id`,`admin_id`,`created_at`,`updated_at`)
values (1,'credit',100,'annual tour','2016-01-01',2,7,2,'2016-11-24 10:28:35','2016-11-29 12:21:08'),
(2,'debit',200,'Product Sale','2016-11-19',6,5,2,'2016-11-24 10:33:02','2016-11-29 12:21:12'),
(3,'debit',250,'Product Sale','2016-11-19',6,4,2,'2016-11-24 10:33:11','2016-11-29 12:21:43'),
(4,'credit',300,'Product Sale','2015-01-27',6,4,2,'2016-11-24 10:33:14','2016-11-29 12:21:53'),
(5,'credit',450,'Product Sale','2016-01-29',5,2,2,'2016-11-24 10:33:17','2016-11-29 12:21:58'),
(6,'debit',210,'Product Sale','2016-11-19',6,4,2,'2016-11-24 10:33:20','2016-11-29 12:22:17'),
(7,'credit',350,'Internal','2016-11-30',14,3,1,'2016-11-24 13:04:04','2016-11-29 12:22:28');


MySQL Query:

SELECT trx_id,staff_id
, SUM(COALESCE(CASE WHEN trx_type = 'debit' THEN trx_amount END,0)) total_debits
, SUM(COALESCE(CASE WHEN trx_type = 'credit' THEN trx_amount END,0)) total_credits

, SUM(COALESCE(CASE WHEN trx_type = 'debit' THEN trx_amount END,0))
- SUM(COALESCE(CASE WHEN trx_type = 'credit' THEN trx_amount END,0)) balance
FROM erp_transactions GROUP BY staff_id HAVING balance <> 0 ORDER BY trx_id;


OutPut Result:

enter image description here

Answer
 select s.*,
        s.debit - s.credit as Balance,
        @RunningBalance:= @RunningBalance + s.debit - s.credit RunningBalance
from 
(
select min(trx_id) trx_id,t.staff_id,
    sum(case when trx_type = 'debit' then trx_amount else 0 end) as Debit,
    sum(case when trx_type = 'credit' then trx_amount else 0 end) as Credit
from  trans t
group by staff_id 
order by trx_id
) s,
(Select @RunningBalance:=0) rb
order by s.trx_id

Result

    +--------+----------+-------+--------+---------+----------------+
| trx_id | staff_id | Debit | Credit | Balance | RunningBalance |
+--------+----------+-------+--------+---------+----------------+
|      1 |        7 |     0 |    100 |    -100 |           -100 |
|      2 |        5 |   200 |      0 |     200 |            100 |
|      3 |        4 |   460 |    300 |     160 |            260 |
|      5 |        2 |     0 |    450 |    -450 |           -190 |
|      7 |        3 |     0 |    350 |    -350 |           -540 |
+--------+----------+-------+--------+---------+----------------+