On Wed, Dec 10, 2008 at 10:52 PM, Rahat Bashir <rahat.bashir@xxxxxxxxx> 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 -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
