Re: efficient count/join query

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