[Query] Left join, AVG, invalid use of group function

[Dauntless]

Hi,

 

This is my first post here. I hope I'm not braking any rules, but I couldn't find a sticky with guidelines ...

 

Situation:

- pictures (ID, gender, ...)

- votes (ID, score)

 

votes.ID corresponds to pictures.ID (so votes.ID is NOT a unique key or something like that)

 

Every time someone votes, it's added to the votes table.

 

My goal:

- get all the pictures of gender = 0 (fe)

- add a field 'score' with the average of the votes table for the corresponding row

 

My attempt:

SELECT pictures.src, pictures.name, pictures.ID, pictures.gender, pictures.birthday , AVG( votes.score ) AS score
FROM pictures
LEFT JOIN votes
ON votes.ID = pictures.ID
WHERE gender =0
GROUP BY pictures.src, pictures.name, pictures.ID, pictures.gender, pictures.birthday
ORDER BY AVG( votes.score ) DESC
LIMIT 0 , 30 

 

I get an error:

#1111 - Invalid use of group function

 

Why am I getting this error and how can I fix it? (If my query is entirely wrong, please help me correct it)

 

If anything is unclear, please let me know!

 

Greetings,

Dauntless

 

EDIT

Found the sticky, really weird it wasn't showing at first ...

MySQL client version: 4.1.12

 

[alexweber15]

try this:

 

- run the query without the GROUP BY clause and see if it returns the correct info

 

- if yes, try

GROUP BY score

(using the alias you gave it) - not sure if this has any impact at all but I'd try anyway to see what happens

- if not, make sure pictures.gender is storing an integer value because that's what you're comparing it with in your WHERE clause....again, I'm not sure how forgiving MySQL is with treating a string of numbers ("123") with a number (123) but if its not selecting anything try that

 

without a similar db schema to run queries against its kind of hard to test...

gl!

[Dauntless]

Unfortunately, I'm getting the exact same error ...

 

Here are the table structure & some entrees:

CREATE TABLE `pictures` (
  `src` varchar(200) NOT NULL default '',
  `name` varchar(40) NOT NULL default '',
  `id` int(11) NOT NULL auto_increment,
  `gender` tinyint(4) NOT NULL default '0',
  `votes` int(11) NOT NULL default '1',
  `score` int(11) NOT NULL default '0',
  `surname` varchar(100) NOT NULL default '',
  `adres` varchar(200) NOT NULL default '',
  `city` varchar(100) NOT NULL default '',
  `zipcode` int(11) NOT NULL default '0',
  `birthday` date NOT NULL default '0000-00-00',
  `email` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=109 ;

 

INSERT INTO `pictures` VALUES ('95_1c61.jpg', 'sdqfsdf', 95, 0, 2, 10, 'sdqfsqd', 'qsdfsdq', 'sdqfsqdf', 57, '0000-00-00', 'qsdf@qdsf.com');
INSERT INTO `pictures` VALUES ('107_1c61.jpg', 'name', 107, 1, 1, 0, 'doe', 'street', 'adres', 2345, '0000-00-00', 'info@mydomain.com');

(Some quick testing entrees)

CREATE TABLE `votes` (
  `ID` int(11) NOT NULL default '0',
  `score` int(11) NOT NULL default '0'
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

INSERT INTO `votes` VALUES (95, 7);
INSERT INTO `votes` VALUES (95, 4);
INSERT INTO `votes` VALUES (95, 2);
INSERT INTO `votes` VALUES (107, 2);
INSERT INTO `votes` VALUES (107, 2);
INSERT INTO `votes` VALUES (107, ;

 

(If no votes are found, '0' should be taken as the score)

 

I hope these can help

[Dauntless]

I tested it on the live site (mysql 5.0.51a) the query is successful and even returns the correct result!

 

How come it doesn't work on my server (4.1.12) ?

[fenway]

Tested what?