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(),COUNT(),...) 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 like this:

$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
GROUP BY NULL 
LIMIT 0 , 10";

it only shows one result instead of 10
any help would be greatly appriciated

Scott

[Markus]

This would be more suited to the mysql forum.

try there :)

[ronverdonk]

This is a MySQL problem/question, so you are in the wrong forum!
I will move this thread.

moderator

[ronverdonk]

The way you SUM is bound to lead to 1 result only.
Please explain in wordt what you want to accomplish and show some table describe, that way it will be much clearer what you want to achieve.

ROnald

[ScottCase]

Hello

What i am doing is getting the top 10 referrers.

the html table has the following fields

name jvreferals regularreferals totalreferals


i need the sql to add the jvreferals and regular referals together and then display the top 10 sorted by the total of jvreferals+regularreferals


scott

[ronverdonk]

I must confess that I still do not understand comparing your explanation to your SQL code.

Let me give it a try:

1. assuming you have only 1 member per table and you want to total their 2 columns per name, soted on total desc, then what is wrong with the following statement

select name, jvreferals+regularreferals as total from members order by total desc limit 10;

2. assuming you have > 1 member per table and you want to GROUP total their 2 columns by name, and sort on total desc.

 select name, sum(jvreferals)+sum(regularreferals) as total from members group by name order by total desc limit 10;

Ronald