12,504,695 members (56,660 online)
Rate this:
See more:
haiii...

I am stuck with a query of Transaction statement (balance sheet )...

I want calculate the balance of credit and debit of a customer...

Can you help me to develop query for calculate the balance ..(in mysql) .here is the normal structure..
```                                                                      [ opening Balance - 0.00 ]

Transaction Date      Transaction Description   Debit     Credit          balance
---------------      ----------------------    -----     -----            --------
01/04/2012          maintance charge          2800       0.00              -2,800.00

01/04/2012           water charge             750.00     0.00              -3,550.00

16/04/2012           (...)                     0.00      8,400.00           4,850.00

23/04/2013           (..)                      0.00      8,400.00           13,250.00```

plz give a query to find the balance....

I think the equation is [(Credit - Debit) + previous.Balance]

Posted 11-Feb-13 21:54pm
Updated 11-Feb-13 22:16pm
v3
Irbaz Haider Hashmi 12-Feb-13 4:00am

What have you done so far. Provide your work.
Also provide the table structure.
aravindnass 12-Feb-13 6:22am

I just tried but ..failed..here is my query

SELECT q.`credit`,q.`debit`
,@bal:=IF(@Id=q.`id`, @bal + (q.credit - q.debit), q.credit - q.debit) AS balance
FROM Transcation AS q

Rate this:

## Solution 1

Have a look here: http://www.tutorialspoint.com/mysql/mysql-sum-function.htm[^]

```--total balance
SELECT (SUM(debit)*-1) + SUM(credit) AS TotalBalance
FROM Trasaction

--daily balance
SELECT TransDate, (SUM(debit)*-1) + SUM(credit) AS DailyBalance
FROM Trasaction
GROUP BY TransDate

--period of time balance
SELECT (SUM(debit)*-1) + SUM(credit) AS PeriodBalace
FROM Trasaction
WHERE TransDate BETWEEN CURDATE() AND ADDDATE(CURDATE() INTERVAL -30 DAY)

--customer balance
SELECT CustId, (SUM(debit)*-1) + SUM(credit) AS CustomerBalance
FROM Trasaction
GROUP BY CustId
```
v2
aravindnass 14-Feb-13 5:15am

Thank you for your answer ..But i need Separate Balance of each customer...
I think the equation is [(Credit - Debit) + previous.Balance] to find ..but I am unable to write the query... plz help me...
Maciej Los 14-Feb-13 5:18am

Have a look at the last example...
aravindnass 14-Feb-13 5:53am

getting error here:
Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ') AS DailyBalace
FROM Trasaction
GROUP BY CustId
LIMIT 0, 1000' at line 1
Maciej Los 14-Feb-13 5:56am

Sorry, remove last ")"
Rate this:

Rate this:

## Solution 2

```you will get the last Balance by using this query

SELECT balance FROM Table name ORDER  BY date DESC LIMIT  1

Balance = (last Previous Balance + Credit) - Debit

Top Experts
Last 24hrsThis month
 OriginalGriff 348 Maciej Los 298 ppolymorphe 175 Karthik Bangalore 137 F-ES Sitecore 130
 OriginalGriff 7,143 ppolymorphe 3,954 Maciej Los 3,621 David_Wimbley 2,907 Karthik Bangalore 2,851