X-Original-To: pgsql-sql@postgresql.org
Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id EC446475BF9
	for <pgsql-sql@postgresql.org>; Fri,  7 Feb 2003 09:23:33 -0500 (EST)
Received: from stan.ringways.co.uk (hub.ringways.co.uk [213.38.87.130])
	by postgresql.org (Postfix) with ESMTP id C0913475A6D
	for <pgsql-sql@postgresql.org>; Fri,  7 Feb 2003 09:23:24 -0500 (EST)
Received: from gary.ringways.co.uk ([10.1.1.2])
	by stan.ringways.co.uk with esmtp (Exim 3.22 #1)
	id 18h9XT-0006h0-00; Fri, 07 Feb 2003 14:31:35 +0000
Content-Type: text/plain;
  charset="iso-8859-1"
From: Gary Stainburn <gary.stainburn@ringways.co.uk>
Organization: Ringways Garages Ltd
To: Tomasz Myrta <jasiek@klaster.net>
Subject: Re: efficient count/join query
Date: Fri, 7 Feb 2003 14:21:14 +0000
User-Agent: KMail/1.4.3
Cc: pgsql-sql@postgresql.org
References: <200302071007.28697.gary.stainburn@ringways.co.uk>
	<200302071209.55330.gary.stainburn@ringways.co.uk>
	<3E43B402.1090301@klaster.net>
In-Reply-To: <3E43B402.1090301@klaster.net>
MIME-Version: 1.0
Message-Id: <200302071421.14672.gary.stainburn@ringways.co.uk>
Content-Transfer-Encoding: quoted-printable
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200302/157
X-Sequence-Number: 11555

On Friday 07 Feb 2003 1:26 pm, Tomasz Myrta wrote:
> Gary Stainburn wrote:
> > On Friday 07 Feb 2003 10:48 am, Tomasz Myrta wrote:
>
> <cut>
>
> > Hi Tomasz,
> >
[snip]
>
> 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=3Drjid and hsid=3Drosid)
> group by hjid,rjid,hsid,rosid;
>
> Regards,
> Tomasz Myrta

Many appologoes Tomasz.

Because your select *LOOKED* wrong to me, I didn't even try it.  Upon looki=
ng=20
at it again I can see what you're doing.  When I tried, it complained about=
=20
the counts and grouping, so I moved the count(*) to a sub-select and change=
d=20
the coalesce and it's working.

Thanks,

Gary

>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

--=20
Gary Stainburn
=20
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=20=
=20=20=20=20