Re: SUM and JOIN together

Rogue wrote:

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

You're trying to do completely unrelated sums. The join really has no relevance to this (you're not *really* trying to join records). The only way to do this in one query (I think) is to use subqueries, which isn't in mysql yet. I'd suggest doing multiple queries. -- -- paperCrane <Justin Patrin>