List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:September 15 2007 5:19am
Subject:Re: Problem with query
View as plain text  
Naz,

That query logic runs without a error on the server I have to hand 
(5.0.37), but it has three issues:

(i) unless there is an exceptionless 1:1 relationship between 
group_post_mod_option.option_id and group_post_mod_option.option_name, 
results for the latter column will be meaningless because it is not 
aggregated. Absent a 1:1 relationship, you need a subquery to fetch the 
name.

(ii) the WHERE condition group_post_moderation.group_post_id = 37 will 
remove all NULL rows from the right side of the join, turning the OUTER 
JOIN effectively into an INNER JOIN, so you might as well write it as an 
INNER JOIN

(iii) why not use the alias `count` in the ORDER BY clause (the query 
engine would likely spot that)?

So that would give something like...

SELECT
  o.option_id,
  (SELECT option_name FROM group_post_mod_option.option_name
   WHERE option_id = o.option_id) AS name,
  COUNT(m.group_post_moderation_option) AS count
FROM group_post_mod_option o
JOIN group_post_moderation m
  ON o.option_id = m.group_post_moderation_option
  AND m.group_post_id = 37
GROUP BY o.option_id
ORDER BY count, o.option_name;

PB

-----

Naz Gassiep wrote:
> I am running this query:
>
> SELECT group_post_mod_option.option_id,       
> group_post_mod_option.option_name,       
> COUNT(group_post_moderation.group_post_moderation_option) AS count 
> FROM group_post_mod_option LEFT OUTER JOIN group_post_moderation ON 
>            (group_post_mod_option.option_id = 
> group_post_moderation.group_post_moderation_option             AND 
> group_post_moderation.group_post_id = 37) GROUP BY 
> group_post_mod_option.option_id ORDER BY 
> COUNT(group_post_moderation.group_post_moderation_option),         
> group_post_mod_option.option_name;
>
>
> And getting this error:
> 'Invalid use of group function'
>
> Without the "ORDER BY" clause, the query works just fine. Anyone know 
> what's going on?
>
> Regards,
> - Naz.
>
>
Thread
Problem with queryNaz Gassiep15 Sep
  • Re: Problem with queryPeter Brawley15 Sep