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