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
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
