Re: SUM and JOIN together

Unless you are using 4.1 (which allows subqueries), you should perform 3 queries, the first 2 creating temporary tables: - sum - difference - left join Ignatius _________________________ ----- Original Message ----- From: "rogue" <rogue@mail.rightcode.net> To: <php-db@lists.php.net> Sent: Thursday, February 19, 2004 04:24 Subject: [PHP-DB] SUM and JOIN together > Hi all, > > Not sure how to handle this situation and was hoping someone with a > bigger brain then me could help. > > 2 tables > > tbl_hoursbought > ------------------------------------ > id | client | hours > -------------------- > 1 | test | 5 > 2 | test | 10 > > > tbl_hoursused > > ------------------------------------ > id | client | hours > -------------------- > 1 | test | 3 > > > What I am trying to do is sum the hours bought and the hours used, then > subtract the values to get the available hours. The problem I am having > is that I get a multiple (based on how many rows are in the first > table) of the value in the second table. Here is my query: > > select sum(tbl_hoursbought.hours) as bought, sum(tbl_hoursused.hours) > as used > from tbl_hoursbought > left join tbl_hoursused > on tbl_hoursbought.client = tbl_hoursused.client > and tbl_hoursbought.client = $id > > > So my results return 15 for bought (which is correct) but I get 6 for > used since there are 2 rows in bought (I think). I figure I could just > divide that by the number of results, but is there a 'correct' way of > doing it in the SQL statement? > > Thanks for any help. > Please copy me with any replies as I am on the digest. > > thanks, > rogue > > -- > PHP Database Mailing List (http://www.php.net/) > To unsubscribe, visit: http://www.php.net/unsub.php > >