[PHP] Need help on MySQL query
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 NULL1.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 NULL4500.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 depositbalance -- --- -- - 1 2008-12-01 00:00:00 NULL1.00 1.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 NULL4500.00 8500.00 5 2008-12-06 00:00:00 500.00 1500.00 9500.00 Thanks in advance -- Rahat Bashir Dhaka, Bangladesh
Re: [PHP] Need help on MySQL query
On Thu, Dec 11, 2008 at 4:52 PM, Rahat Bashir [EMAIL PROTECTED]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; SELECT *, deposit-withdrawn AS balance FROM transaction Although i would suggest a transaction table to only have positive and negative balances. Then you can do all sorts of things with it like get the sum of all transactions a lot easier etc. e.g. SELECT SUM(balance) AS user_balance FROM transaction WHERE user_id=1 if you have a user id e.g. Are you working for a bank? :-) id date withdrawn deposit -- --- -- 1 2008-12-01 00:00:00 NULL1.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 NULL4500.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 depositbalance -- --- -- - 1 2008-12-01 00:00:00 NULL1.00 1.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 NULL4500.00 8500.00 5 2008-12-06 00:00:00 500.00 1500.00 9500.00 Thanks in advance -- Rahat Bashir Dhaka, Bangladesh
Re: [PHP] Need help on MySQL query
On Wed, Dec 10, 2008 at 10:52 PM, Rahat Bashir [EMAIL PROTECTED] 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 NULL1.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 NULL4500.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 depositbalance -- --- -- - 1 2008-12-01 00:00:00 NULL1.00 1.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 NULL4500.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 1.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
Re: [PHP] Need help on MySQL query
Rahat Bashir wrote: I need your help on writing a MySQL query. I see nothing php related in your question, try a mysql list. -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
