[RESOLVED] Adding single value with multiple rows.

[Abarak]

I am having problems getting the net worth of a specific character in a game I am developing. I have two tables; one containing the character information and the other holding the characters business information. The network is determined by adding values of each of the characters business and adding the cash on hand of the character.

Here is the query I have already made:

SELECT SUM(`characters`.`cash`+`business`.`A`+(`business`.`B`*800)+(`business`.`C`*80)+(`business`.`D`*50)+(`business`.`E`*60)+(`business`.`F`*250)+(`business`.`G`*1000)+(`business`.`H`*1750)+(`business`.`I`*15000) ) AS `total` FROM `business` Inner Join `characters` ON `business`.`charID` = `characters`.`charID` WHERE `characters`.`charID` = '1'

Everything works just fine but the characters.cash is added to the total twice and it needs to be added once. The reason it is added twice is because the character has two businesses.

Any help that can be provided would be most appreciated.

Abarak

[tomhath]

Try something like this (untested). You need to sum the business amounts in a subselect so it will come back as a single row to join with the cash table:

 SELECT c.cash + b.cash
 FROM cash c
 INNER JOIN (select charID,   

            sum( B*800 + C*80 + D*50 + E*60 +
                  F*250 + G*1000 + H*1750 + I*15000  ) as cash
            from business group by charID 
            ) as b
 ON c.charID = b.charID
 WHERE c.charID = '1' 

[Abarak]

Good call. Man, you can do some really wicked stuff with SQL statements. Now it’s time to see how far I can take it. 😃

Thanks for the assistance, the code worked just fine. However, I did have to remove the 'c' after the first FROM statement.

Abarak