[SOLVED] Complex query, for me at least...

[jcbarr]

Okay here is the query that I have right now

[code]SELECT team, SUM(ch+ph+sp) as bvalue, SUM(`as`+co+pavg) as pvalue FROM rosters GROUP BY team ORDER BY bvalue+pvalue[/code]

So here is the question, and I'm not sure if I can do this or not.

I only want to select the SUM(ch+ph+sp) where pos<>P and I only want to select SUM(`as`+co+pavg) from where pos=P. I want to GROUP BY team and order by the value of bvalue+pvalue...

So can I do this? The above query isn't working for me when trying to use it with mysql_fetch_assoc I am told that it is not a valid argument.

So, does anyone know how to do this? Can I do it in one query?

[btherl]

Hmm.. you could use this trick:

[code=php:0]SELECT team, SUM(IF(pos<>P,ch+ph+sp,0)) as bvalue, SUM(IF(pos=P,`as`+co+pavg,0)) as pvalue FROM rosters GROUP BY team ORDER BY bvalue+pvalue[/code]

My syntax may not be 100% correct but the idea is sound.  Otherwise you can just use 2 queries, if the overhead isn't a large factor.

[jcbarr]

Actually it works by just adding the two sums, like this

[code=php:0]$sql = 'SELECT team, SUM(IF(pos<>\'P\',ch+ph+sp,0))+SUM(IF(pos=\'P\',`as`+co+pavg,0)) as value FROM rosters GROUP BY team ORDER BY value DESC';[/code]

Thanks a ton for the help.