Cant get sql statement to work in mysql 5

[ScottCase]

Hello,

I have this sql statement and it works fine with mysql v4

$query = "SELECT id, name, jv_signups, mem_signups, (
SELECT SUM( jv_signups )
) + (
SELECT SUM( mem_signups ) ) AS total
FROM members
WHERE clicktracking >0
AND LEVEL =2
ORDER BY total DESC
LIMIT 0 , 10";

but on mysql v5 i get the following error:

DATABASE ERROR: ERRNO: 1140 ERROR: Mixing of GROUP columns (MIN(),MAX(),CO UNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause MSG:Invalid SQL: SELECT id, name, jv_signups, mem_signups, ( SELECT SUM( jv_signups ) ) + ( SELECT SUM( mem_signups ) ) AS total FROM members WHERE clicktracking >0 AND LEVEL =2 LIMIT 0 , 10



if i add the group by null it only shows one result instead of 10


any help would be greatly appriciated


Scott

[Delerna]

I have not used MySQL but I wouldn't imagine it to be that much different to any other version of sql. Your code, as written, looks very strange to me.

in particular this bit
(SELECT SUM( jv_signups ) ) + ( SELECT SUM( mem_signups ) ) AS total


[code=sql]
SELECT id,
name,
jv_signups,
mem_signups,
(SELECT SUM( jv_signups ) ) + ( SELECT SUM( mem_signups ) ) AS total
FROM members
WHERE clicktracking >0
AND LEVEL =2
ORDER BY total DESC
LIMIT 0 , 10
[/code]

shouldn't it be more like
[code=sql]
SELECT id,
name,
SUM( jv_signups ) + SUM( mem_signups ) AS total
FROM members
WHERE clicktracking >0
AND LEVEL =2
GROUP BY id,name
ORDER BY total DESC
LIMIT 0 , 10
[/code]

I don't know about limit 0,10 so I left it where it was