Arun Kumar Arun Kumar - 3 months ago 6
SQL Question

Efficient query to get account balance

I have a table with sample data like this: http://sqlfiddle.com/#!9/124b62

What is the best way(efficient query) to get the opening and closing balance of the cashbook account?

One way is to find total credits and subtract it from total debits.

SELECT sum(amount) as credit_total
FROM `cash_book`
WHERE `type` = 'credit'
AND `account_holder_id` =1
AND `created_at` >= '2016-07-31 00:00:00';


SELECT sum(amount) as debit_total
FROM `cash_book`
WHERE `type` = 'debit'
AND `account_holder_id` =1
AND `created_at` >= '2016-07-31 00:00:00';


Can someone suggest a better query?

Answer

Single run total

SELECT sum(case when `type` = 'credit' then -amount else amount end) as total  
FROM `cash_book` 
WHERE  AND `account_holder_id` =1 and  `created_at` >= '2016-07-31 00:00:00';
Comments