# MySql Calculate Opening and Closing Balance

database table name `tbl_log`

``````ID  | month   | open_qty        | in_qty     | out_qty      | balance | item
----------------------------------------------------------------------------
1   | 2023-01 | 0               | 1          | 0            |  1      |  A
2   | 2023-02 | 1               | 2          | 1            |  2      |  A
3   | 2023-03 | 2               | 5          | 2            |  5      |  A
4   | 2023-04 | 5               | 0          | 1            |  4      |  A
``````

I am not able to create an idea for calculating an `opening balance` for each month `where item = A`
I am trying to achieve by using but no idea, I am using MySql query please help to calculate opening balance of the each month

``````SELECT
COALESCE(SUM(QTY),
0) AS `OpeningBalance`
FROM
(
SELECT
`in_qty` + `open_qty` QTY
FROM
`tbl_log` CR
WHERE
CR.item = 'A'
) t
``````

Your DB structure is not right for doing this. Deposits and withdrawals should be a single column. Aside from using a transaction_type column to determine a deposit/withdrawl you could use positive/negative numbers instead. There are pros and cons to doing it that way.

Here is a quick example for Mysql 8.

``````-- Creating the table
CREATE TABLE account_transactions (
ID INT AUTO_INCREMENT PRIMARY KEY,
transaction_date DATE,
transaction_type ENUM('deposit', 'withdrawal'),
amount DECIMAL(10, 2)
);

-- Inserting sample data
INSERT INTO account_transactions (transaction_date, transaction_type, amount) VALUES
('2023-01-01', 'deposit', 1000.00),
('2023-02-03', 'withdrawal', 200.00),
('2023-03-05', 'deposit', 500.00),
('2023-04-07', 'withdrawal', 150.00),
('2023-05-09', 'deposit', 750.00),
('2023-06-11', 'withdrawal', 300.00),
('2023-07-13', 'deposit', 1200.00),
('2023-08-15', 'withdrawal', 500.00),
('2023-09-17', 'deposit', 450.00),
('2023-10-19', 'withdrawal', 250.00);
``````

SHOW RUNNING BALANCE REGISTER

``````SELECT
ID,
transaction_date,
transaction_type,
amount,
SUM(CASE WHEN transaction_type = 'deposit' THEN amount ELSE -amount END)
OVER (ORDER BY transaction_date, ID) AS running_balance
FROM
account_transactions
ORDER BY
transaction_date, ID;
``````

SHOW OPENING BALANCE BY MONTH

``````SELECT
DATE_FORMAT(sub.first_of_month, '%Y-%m') AS month,
COALESCE((
SELECT SUM(CASE WHEN at.transaction_type = 'deposit' THEN at.amount ELSE -at.amount END)
FROM account_transactions at
WHERE at.transaction_date < sub.first_of_month
), 0) AS opening_balance
FROM
(SELECT DISTINCT
LAST_DAY(transaction_date - INTERVAL 1 MONTH) + INTERVAL 1 DAY AS first_of_month
FROM
account_transactions) sub
ORDER BY
sub.first_of_month;
``````
2 Likes

I agree that if youâ€™re building a ledger, the style that benanamen has provided is the way to go.

That said.

opening balanceâ€¦ okayâ€¦ the data will be redundant.

``````UPDATE tbl_log main
INNER JOIN tbl_log preva
ON (preva.month - INTERVAL -1 MONTH) = main.month
SET main.open_qty = preva.balance
WHERE main.month > "2023-01-01";
``````

NOTE: I have converted `month` to an actual DATE column to make this work.

(You can of course just SELECT this information, if youâ€™re just trying to pull it, as a simple SELECTâ€¦LEFT JOIN)

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.