Re: efficient count/join query

Gary Stainburn wrote:
> On Friday 07 Feb 2003 10:48 am, Tomasz Myrta wrote:
>

<cut>
> Hi Tomasz,
>
> I don't think you understand what I mean.
>
> The history table could be thought of as the following SQL statement if the
> data had actually existed. This table actually represents a manually input
> summary of the pre-computerised data.
>
> insert into history
> select rosid, rojid, count(*) from roster_staff group by rssid, rsgsid;
>
> If I have a history of
>
> hsid | hjid | hcount
> ------+------+--------
> 1 | 2 | 3
> 1 | 3 | 1
> 5 | 5 | 4
> 6 | 5 | 3
> 9 | 4 | 4
> 14 | 5 | 4
>
> and I have a roster of
>
> rodate | rogid | rojid | rosid
> -----------+-------+-------+-------
> 2003-02-15 | 1 | 2 | 1
> 2003-02-15 | 1 | 5 | 5
> 2003-02-16 | 1 | 5 | 1
>
> I want my view to show
>
> hsid | hjid | hcount
> ------+------+--------
> 1 | 2 | 4
> 1 | 3 | 1
> 1 | 5 | 1
> 5 | 5 | 5
> 6 | 5 | 3
> 9 | 4 | 4
> 14 | 5 | 4
>

I understood your problem well and I just thought some idea will be enough to continue work.

Here is detailed query for your problem:

create view some_view as
select
coalesce(hjid,rjid) as jid,
coalesce(hsid,rsid) as sid,
coalesce(hcount,1)+count(*)-1 as hcount
from
history
full outer join roster on (hjid=rjid and hsid=rosid)
group by hjid,rjid,hsid,rosid;

Regards,
Tomasz Myrta