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
selectcoalesce(hjid,rjid) as jid,coalesce(hsid,rsid) as sid,coalesce(hcount,1)+count(*)-1 as hcount
fromhistoryfull outer join roster on (hjid=rjid and hsid=rosid)
group by hjid,rjid,hsid,rosid;

Regards,
Tomasz Myrta