Re: subquery/alias question

On Sep 26, 2007, at 7:41 , Madison Kelly wrote:

>   Unfortunately, in both cases I get the error:

Um, the two cases could not be giving the same error as they don't
both contain the syntax that the  error is complaining about: the
first case uses count in a subquery so it couldn't throw this exact
error.

> nmc=> SELECT dom_id, dom_name, COUNT(usr_dom_id) AS usr_count FROM
> domains JOIN users ON (usr_dom_id=dom_id) HAVING COUNT (usr_dom_id)
> > 0 ORDER BY dom_name;
> ERROR:  syntax error at or near "COUNT" at character 25
> LINE 1: SELECT dom_id, dom_name COUNT(usr_dom_id) AS usr_count
> FROM ...

The error message doesn't match the query you've provided. Note that
in the line marked LINE 1, there's no comma after dom_name, which I
assume is what the server is complaining about. However, the query
you show *does* have this comma. Something isn't right. Is this an
exact copy and paste from psql?

>   I've been struggling with some deadlines, so for now I'm using just:
>
> SELECT d.dom_id, d.dom_name FROM domains d WHERE (SELECT COUNT(*)
> FROM users u WHERE u.usr_dom_id=d.dom_id) > 0 ORDER BY d.dom_name ASC;
>
>   Which gives me just the domains with at least one user under
> them, but not the count. This is not ideal, and I will have to come
> back to it next week. In the meantime, any idea what the GROUP BY
> error is?

Ah. You haven't actually shown us a GROUP BY error. A GROUP BY clause
is needed when you've got columns that aren't included in the
aggregate (COUNT in this case), e.g.,

select dom_id,
        dom_name,
        usr_count
   from domains
   natural join (select usr_dom_id as dom_id,
                        count(usr_dom_id) as usr_count
                   from users
                   group by dom_id) u
   where usr_count > 0
   order by dom_name;

select dom_id, dom_name, count(usr_dom_id) as usr_count
   from domains
   join users on (usr_dom_id = dom_id)
   group by dom_id, dom_name
   having count(usr_dom_id) > 0
   order by dom_name;

Michael Glaesemann
grzm seespotcode net