Skip site navigation (1) Skip section navigation (2)

Re: subquery/alias question

From: Madison Kelly <linux(at)alteeve(dot)com>
To: Michael Glaesemann <grzm(at)seespotcode(dot)net>
Cc: Alvaro Herrera <alvherre(at)commandprompt(dot)com>, PostgreSQL General <pgsql-general(at)postgresql(dot)org>
Subject: Re: subquery/alias question
Date: 2007-09-26 12:41:14
Message-ID: 46FA536A.9020908@alteeve.com (view raw or flat)
Thread:
Lists: pgsql-general
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

In response to

Responses

pgsql-general by date

Next:From: Alvaro HerreraDate: 2007-09-26 12:55:38
Subject: Re: subquery/alias question
Previous:From: Moiz KothariDate: 2007-09-26 10:28:08
Subject: Re: pgpoolAdmin:No such file or directory in /var/www/html/pgpoolAdmin-1.0.0/libs/Smarty.class.php on line 1258

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group