By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,378 Members | 1,435 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,378 IT Pros & Developers. It's quick & easy.

Cant get sql statement to work in mysql 5

P: 3
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 it only shows one result instead of 10


any help would be greatly appriciated


Scott
Mar 27 '08 #1
Share this Question
Share on Google+
1 Reply


Delerna
Expert 100+
P: 1,134
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


Expand|Select|Wrap|Line Numbers
  1. SELECT id, 
  2.        name,
  3.        jv_signups, 
  4.        mem_signups,
  5.       (SELECT SUM( jv_signups ) ) + ( SELECT SUM( mem_signups ) ) AS total      
  6. FROM members
  7. WHERE clicktracking >0
  8.    AND LEVEL =2
  9. ORDER BY total DESC 
  10. LIMIT 0 , 10
  11.  
shouldn't it be more like
Expand|Select|Wrap|Line Numbers
  1. SELECT id, 
  2.        name,
  3.        SUM( jv_signups )  +  SUM( mem_signups )  AS total
  4. FROM members
  5. WHERE clicktracking >0
  6.    AND LEVEL =2
  7. GROUP BY id,name
  8. ORDER BY total DESC 
  9. LIMIT 0 , 10
  10.  
I don't know about limit 0,10 so I left it where it was
Mar 30 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.