Hello Friends, Today I am going to share that how to calculate balances from debit and credit column in MySQL. If you have to show individual transactions and also want to keep a running balance column at the same time then this article will surely help you. Actually this query I was required to create in one of my project where I was developing a wallet system for user, so this concept can be used to create wallet system in your own project.
Create Table Query
The below create table query will create a table with name user_transactions.
|
1 2 3 4 5 6 7 8 |
CREATE TABLE `user_transactions` ( `id` bigint(20) NOT NULL PRIMARY KEY AUTO_INCREMENT, `user_id` int(11) NOT NULL, `txn_date` datetime NOT NULL, `credit` decimal(10,2) NOT NULL, `debit` decimal(10,2) NOT NULL, `comments` text NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; |
Insert Query
The below query will add some data to table so that we can run select query on it.
|
1 2 3 4 5 6 7 8 9 10 |
INSERT INTO `user_transactions` (`id`, `user_id`, `txn_date`, `credit`, `debit`, `comments`) VALUES (1, 2, '2018-07-21 04:26:14', '453.00', '0.00', 'test1'), (2, 7, '2018-08-17 00:00:00', '50.00', '0.00', 'test2'), (4, 7, '2018-08-18 04:34:59', '0.00', '10.00', 'test3'), (5, 7, '2018-08-18 04:38:13', '0.00', '5.00', 'Order Placed'), (6, 7, '2018-08-18 04:59:19', '0.00', '10.00', 'test4'), (7, 7, '2018-08-18 09:02:21', '0.00', '25.00', 'Order Placed '), (8, 9, '2018-09-03 12:43:47', '57.25', '0.00', 'Received credits'), (9, 9, '2018-09-03 12:43:47', '0.00', '3.00', 'Charged Commission'), (10, 9, '2018-09-04 10:46:05', '21.00', '0.00', 'Received credits') |
Select Query
Finally the below query will result all the transactions with balance column of user whose id is 7.
|
1 2 3 |
SELECT id, user_id, txn_date, credit, debit, COALESCE(((SELECT SUM(credit) FROM user_transactions b WHERE b.id <= a.id AND user_id = '7') - (SELECT SUM(debit) FROM user_transactions b WHERE b.id <= a.id AND user_id = '7')), 0) as balance, comments FROM user_transactions a WHERE user_id = '7' ORDER BY id DESC |
Output
Thanks for Visiting
Please do share if you find it useful
Comments