Re: efficient count/join query - Mailing list pgsql-sql

On Friday 07 Feb 2003 12:09 pm, Gary Stainburn wrote:
> On Friday 07 Feb 2003 10:48 am, Tomasz Myrta wrote:
> > Gary Stainburn wrote:
> > > Hi folks,
> > >
> > > I've got two tables, first a history table containing tallies for
> > > staff/jobs prior to going live, and second a roster table showing date,
> > > diagram, job with one record per person per job per day.  the tables
> > > are:
> > >
> > > create table history (
> > > hsid    int4 not null references staff(sid),
> > > hjid    int4 not null references jobs(jid),
> > > hcount    int4,
> > > primary key (hsid,hjid));
> > >
> > > create table roster (
> > > rodate    date not null,
> > > rogid    int4 not null references diagrams(gid),
> > > rojid    int4 not null references jobs(jid),
> > > rosid    int4 references staff(sid),
> > > primary key (rodate, rogid, rojid));
> > >
> > > What's the best/quickest/cheapest way to create a view in the format of
> > > the history table but including the details from the roster table for
> > > all records prior to today.
> > >
> > > I've been looking at some form of sub-select/join scheme but as some
> > > will only exist on the history and some will only exist on the roster
> > > while many will exist on both.
> >
> > Hello again.
> >
> > What if they exists in both tables - you need only one row result?
> > If yes, you should use FULL OUTER JOIN and COALESCE.
> >
> > select
> >  coalesce(hjid,rjid) as jid,
> >  coalesce(hsid,rsid) as sid,
> >  hcount,
> >  rodate,
> >  rogid
> > from
> >  history
> >  full outer join roster on (hjid=rjid and hsid=rosid)
> >
> > Using other names for the same field in other tables comes again -
> > If you have the same name for jid and sid, you wouldn't need coalesce.
> >
> > Regards,
> > Tomasz Myrta
>
> 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


Thinking about it, I'm not wanting to perform a join as such, but a merge of
the two selects below, then some form of group by to sum() the two counts.
   select rosid as sid, rojid as jid, count(*) as count                 from roster group by sid, jid order by sid,
jid;  select hsid as sid, hjid as jid, hcount as count                   from history order by sid, jid; 

so that

1    2    1
1    3    2

and

1    3    1
1    4    2

becomes

1    2    1
1    3    3
1    4    2

--
Gary Stainburn
This email does not contain private or confidential material as it
may be snooped on by interested government parties for unknown
and undisclosed purposes - Regulation of Investigatory Powers Act, 2000