Re: COUNT(*) to find records which have a certain number of

From: T E Schmitz <mailreg(at)numerixtechnology(dot)de>
To: Greg Stark <gsstark(at)mit(dot)edu>, pgsql-sql(at)postgresql(dot)org
Subject: Re: COUNT(*) to find records which have a certain number of
Date: 2004-09-20 19:50:34
Message-ID: 414F348A.2020608@numerixtechnology.de
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-patches pgsql-sql

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

In response to

Responses

Browse pgsql-patches by date

  From Date Subject
Next Message LELARGE Guillaume 2004-09-20 20:17:39 Re: French translation updates for 7.4 and 7.5
Previous Message Michael Paesold 2004-09-20 19:09:50 Re: Fix for VACUUM in psql autocommit off

Browse pgsql-sql by date

  From Date Subject
Next Message CHRIS HOOVER 2004-09-20 20:36:00 Help with function
Previous Message Dean Gibson (DB Administrator) 2004-09-20 19:24:49 JOIN performance