Message-ID: <414F348A.2020608@numerixtechnology.de>
Date: Mon, 20 Sep 2004 20:50:34 +0100
From: T E Schmitz <mailreg@numerixtechnology.de>
Reply-To: mailreg@numerixtechnology.de
User-Agent: Mozilla Thunderbird 0.7 (Windows/20040616)
MIME-Version: 1.0
To: Greg Stark <gsstark@mit.edu>, pgsql-sql@postgresql.org
Subject: Re: COUNT(*) to find records which have a certain number of
References: <414EF3E6.9040202@numerixtechnology.de>
	<87llf4hjv2.fsf@stark.xeocode.com>
In-Reply-To: <87llf4hjv2.fsf@stark.xeocode.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

Hello Greg,
You have given me plenty of food for thought. Thank you for taking the 
time.
Currently, the tables have such few records (350, 900, 1000) that 
performance does not come into it, particularly seeing as this was only 
needed for a one-shot report.
However, I have stached your examples away for future reference.

I was feeling a bit guilty about posting such a trivial question. I can 
cobble together some straightforward SQL but I could really do with a 
source of more complex SQL examples.
If you know of any links - that would great and save the list from more 
such questions ;-)

I am correcting a couple of typos below in case someone tries these 
examples out.

Greg Stark wrote:

> select * 
>   from brand 
>   join model on (brand_pk = brand_fk)
>  where (select count(*)
>           from type 
>          where model_fk = model_pk 
>         having sum(case when type = 'xyz' then 1 else 0 end) >= 1
>        ) > 1

...  having sum(case when type_name = 'xyz' ...

> select * 
>   from brand 
>   join model on (brand_pk = brand_fk)
>   join (select model_fk
>           from type 
>          group by model_fk
>         having sum(case when type = 'xyz' then 1 else 0 end) >= 1
>            and count(*) > 1
>        ) on (model_fk = model_pk)
> 

) as somealias on (model_fk = model_pk)

(subquery in FROM must have an alias)

-- 


Regards/Gruß,

Tarlika