very complicated mysql

[bluesaga]

Think your up for it? This is one hell of a query but here goes...

"SELECT *, (sum((unituser.amount * unitslist.mstrength)) + (userareas.level * 100)) AS strength FROM unitslist, unituser, userareas WHERE unitslist.id = unituser.unitid AND ( (( sum((unituser.amount * unitslist.mstrength)) + (userareas.level * 100) * 2) < '".$power."') OR (('".$power."' * 2.5) < sum((unituser.amount * unitslist.mstrength)) + (userareas.level * 100) ) ) AND userareas.id=unituser.username AND userareas.id != '".$id."' GROUP BY unituser.username ORDER BY strength DESC"

This returns invalid use of group function i also know that it is to do with the 'AND ( (( sum((unituser.amount * unitslist.mstrength)) + (userareas.level * 100) * 2) < '".$power."') OR (('".$power."' * 2.5) < sum((unituser.amount * unitslist.mstrength)) + (userareas.level * 100) ) )' part

[sweatje]

do
SELECT unituser.username, sum...
instead of
SELECT *, sum...

[Karl]

You can also simplify a little:

SELECT unituser.username, (sum((unituser.amount * unitslist.mstrength)) + (userareas.level * 100)) AS strength FROM unitslist, unituser, userareas WHERE unitslist.id = unituser.unitid AND ( (( strength * 2) < '".$power."') OR (('".$power."' * 2.5) < strength ) ) AND userareas.id=unituser.username AND userareas.id != '".$id."' GROUP BY unituser.username ORDER BY strength DESC"

[bluesaga]

You can also simplify a little:

SELECT unituser.username, (sum((unituser.amount * unitslist.mstrength)) + (userareas.level * 100)) AS strength FROM unitslist, unituser, userareas WHERE unitslist.id = unituser.unitid AND ( (( strength * 2) < '".$power."') OR (('".$power."' * 2.5) < strength ) ) AND userareas.id=unituser.username AND userareas.id != '".$id."' GROUP BY unituser.username ORDER BY strength DESC"

This says that strength does not exist. Also in response to sweetje it doesn't make a difference

[bluesaga]

oh and btw, if i remove the sum command from the
' AND ( (( sum((unituser.amount * unitslist.mstrength)) + (userareas.level * 100) * 2) < '".$power."') OR (('".$power."' * 2.5) < sum((unituser.amount * unitslist.mstrength)) + (userareas.level * 100) ) ) AND ' bit it doesn't return an error but doesn't work as expected (which is obvious why)

[bluesaga]

so i take no one can help me on this?

Or is it just not possible

(IF YOU FIND THIS POST AS SPAM CLOSE THE TOPIC)

[Hierophant]

What is the value of $power and why do you have a number in single quotes within your query?

Also what happens if you remove the GROUP BY clause?

[bluesaga]

numbers in quotes 'oops' well that doesn't matter anyway, about the GROUP BY clause it doesn't make a difference if i remove it

Thanks any wayy