From: T E Schmitz <mailreg@numerixtechnology.de>
To: Greg Stark <gsstark@mit.edu>
To: pgsql-sql@postgresql.org
Subject: Re: COUNT(*) to find records which have a certain number of
Date: Mon, 20 Sep 2004 20:50:34 +0100
Message-ID: <414F348A.2020608@numerixtechnology.de> (raw)
In-Reply-To: <87llf4hjv2.fsf@stark.xeocode.com>
References: <414EF3E6.9040202@numerixtechnology.de>
<87llf4hjv2.fsf@stark.xeocode.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mailreg@numerixtechnology.de, gsstark@mit.edu
Subject: Re: COUNT(*) to find records which have a certain number of
In-Reply-To: <414F348A.2020608@numerixtechnology.de>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox