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
selectcoalesce(hjid,rjid) as jid,coalesce(hsid,rsid) as sid,coalesce(hcount,0)+  sum(case when rjid is not null then 1
else0 end) as hcount
 
fromhistoryfull outer join roster on (hjid=rjid and hsid=rosid)
group by hjid,rjid,hsid,rosid,hcount;

Tomasz Myrta