From: Tomasz Myrta <jasiek@klaster.net>
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: efficient count/join query
Date: Fri, 07 Feb 2003 15:38:59 +0100
Message-ID: <3E43C503.6010508@klaster.net> (raw)
References: <200302071007.28697.gary.stainburn@ringways.co.uk>
<200302071209.55330.gary.stainburn@ringways.co.uk>
<3E43B402.1090301@klaster.net>
<200302071421.14672.gary.stainburn@ringways.co.uk>
Gary Stainburn wrote:
> On Friday 07 Feb 2003 1:26 pm, Tomasz Myrta wrote:
>
>>Gary Stainburn wrote:
>>
<cut>
>
> Many appologoes Tomasz.
No problem
>
> Because your select *LOOKED* wrong to me, I didn't even try it. Upon looking
> at it again I can see what you're doing. When I tried, it complained about
> the counts and grouping, so I moved the count(*) to a sub-select and changed
> the coalesce and it's working.
There were 2 bugs in this query:
- in group by - add field hcount
- counting when any roster doesn't exist for some history. Here is (I suppose) final query:
create view some_view as
select
coalesce(hjid,rjid) as jid,
coalesce(hsid,rsid) as sid,
coalesce(hcount,0)+
sum(case when rjid is not null then 1 else 0 end) as hcount
from
history
full outer join roster on (hjid=rjid and hsid=rosid)
group by hjid,rjid,hsid,rosid,hcount;
Tomasz Myrta
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: jasiek@klaster.net, gary.stainburn@ringways.co.uk
Subject: Re: efficient count/join query
In-Reply-To: <3E43C503.6010508@klaster.net>
* 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