Re: Need help on MySQL query

On Wed, Dec 10, 2008 at 10:52 PM, Rahat Bashir <rahat.bashir@gmail.com> wrote: > Hi Experts, > > EID Mubarak to all. > > I need your help on writing a MySQL query. > > Scenario: > > CREATE TABLE transaction > ( > id int NOT NULL ATUTO INCREMENT, > date datetime NOT NULL, > withdrawn double (12,2) NULL, > deposit double (12,2) NULL > ); > > SELECT * FROM transaction; > > id date > withdrawn deposit > ----- ----------------------------- > --------------- -------------- > 1 2008-12-01 00:00:00 > NULL 10000.00 > 2 2008-12-02 00:00:00 4000.00 > NULL > 3 2008-12-04 00:00:00 2000.00 > NULL > 4 2008-12-05 00:00:00 > NULL 4500.00 > 5 2008-12-06 00:00:00 500.00 > 1500.00 > > The above is all I have. I want to make query which should output an extra > calculated column named "balance", something like following: > > Expected output from query: > id date > withdrawn deposit balance > ----- ----------------------------- > --------------- -------------- ------------- > 1 2008-12-01 00:00:00 > NULL 10000.00 10000.00 > 2 2008-12-02 00:00:00 4000.00 > NULL 6000.00 > 3 2008-12-04 00:00:00 2000.00 > NULL 4000.00 > 4 2008-12-05 00:00:00 > NULL 4500.00 8500.00 > 5 2008-12-06 00:00:00 500.00 > 1500.00 9500.00 > > Thanks in advance > > > -- > Rahat Bashir > Dhaka, Bangladesh > I would do the running sum in PHP rather than SQL. If your database doesn't have an operator to perform running totals, it is possible but the result usually involves correlated subqueries that usually result in expensive triangular joins. (I think MySQL might have something that will work, but I haven't tried it. STW for MySQL and running sum and you'll find some discussion that should get you going.) If you decide to do the calculation in SQL, you should modify your table definition slightly: CREATE TABLE transaction ( id int NOT NULL AUTO INCREMENT, date datetime NOT NULL, withdrawn double (12,2) NOT NULL DEFAULT 0.0, deposit double (12,2) NOT NULL DEFAULT 0.0 ); The value of withdrawn and deposit should never be NULL. The value of NULL in SQL is essentially an UNKNOWN/MISSING value. That's why SQL calculations that involve NULL yield a NULL value. With the example you posted, it would be like saying "When I went to the bank December 1, I deposited 10000.00 but I don't know how much I withdrew." What would you say the net value of such a transaction would be? In your database, neither withdrawn nor deposit should be UNKNOWN/MISSING. (At least I would hope not!) Andrew