From: Tomasz Myrta <jasiek@klaster.net>
To: Gary Stainburn <gary.stainburn@ringways.co.uk>
Cc: pgsql-sql@postgresql.org
Subject: Re: efficient count/join query
Date: Fri, 07 Feb 2003 14:26:26 +0100
Message-ID: <3E43B402.1090301@klaster.net> (raw)
References: <200302071007.28697.gary.stainburn@ringways.co.uk>
<3E438F0F.5000301@klaster.net>
<200302071209.55330.gary.stainburn@ringways.co.uk>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: jasiek@klaster.net, gary.stainburn@ringways.co.uk
Subject: Re: efficient count/join query
In-Reply-To: <3E43B402.1090301@klaster.net>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox