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