Michael Glaesemann wrote:
On Sep 25, 2007, at 17:30 , Alvaro Herrera wrote:
Michael Glaesemann wrote:
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) u
where usr_count > 0
order by dom_name;
Maybe the usr_count should be tested in a HAVING clause instead of
WHERE? And put the count(*) in the result list instead of a subselect.
That feels more natural to me anyway.
I believe you'd have to write it like
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;
I don't know how the performance would compare. I think the backend is
smart enough to know it doesn't need to perform two seq scans to
calculate count(usr_dom_id), but I wasn't sure.
Madison, how do the two queries compare with explain analyze?
Thanks for your reply!
Unfortunately, in both cases I get the 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 ...
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? If not,
I'll read through the docs on 'GROUP'ing once I get this deadline out of
the way.
Thank you all for your help! I am sure I will have more question(s)
next week as soon as I can get back to this.
Madi
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
