MIME-Version: 1.0
References: <m3frw468r5.fsf@nitrogen.jhcloos.org>
In-Reply-To: <m3frw468r5.fsf@nitrogen.jhcloos.org>
From: Steve Midgley <science@misuse.org>
Date: Mon, 1 Apr 2024 16:13:09 -0700
Message-ID: 
 <CAJexoS+gyE6-GqRqwZ+e79dX0JSi8KOYKLFiaJNVC1L0mNMhTA@mail.gmail.com>
Subject: Re: help with a particular multi-table query
To: James Cloos <cloos@jhcloos.com>
Cc: pgsql-sql@lists.postgresql.org
Content-Type: multipart/alternative; boundary="000000000000bcd104061511220d"
Archived-At: 
 <https://www.postgresql.org/message-id/CAJexoS%2BgyE6-GqRqwZ%2Be79dX0JSi8KOYKLFiaJNVC1L0mNMhTA%40mail.gmail.com>
Precedence: bulk

--000000000000bcd104061511220d
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

On Mon, Apr 1, 2024 at 3:03=E2=80=AFPM James Cloos <cloos@jhcloos.com> wrot=
e:

> I'm attempting a three column select from two tables, where only a
> single column from each of the tables matters.
>
> t1.date and t2.time are both timestamptz.
>
> I want the three columns to be:
>
> t1.date::date
>
> t1.date - lag(t1.date,1) over (order by date asc) days,
>
> and count(t2.time) from the interval lag(t1.date,1) and t1.date.
>
> but that syntax of course fails do to the placements I've tried for thae
> between.
>
> I tried a sub-query but got what looked like an outer join.
>
> I want exactly count(*) from t1 rows in the result.
>
> What trick am I missing?
>
> I'm a little confused by your SQL, which appears to be incomplete? Could
you give some code to create a simple table, populate it with a few sample
rows, and then a full SQL query of what you are trying to accomplish? Also
include what you get back from your query and what you wish you were
getting back, in terms of result sets..

The main thing I'm missing is how t1 and t2 are joined.. I can't see that,
so it's hard to understand why your query is not giving you the results you
want.

Best,
Steve

--000000000000bcd104061511220d
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div><br><div class=3D"gmail_quote">=
<div dir=3D"ltr" class=3D"gmail_attr">On Mon, Apr 1, 2024 at 3:03=E2=80=AFP=
M James Cloos &lt;<a href=3D"mailto:cloos@jhcloos.com">cloos@jhcloos.com</a=
>&gt; wrote:<br></div><blockquote class=3D"gmail_quote" style=3D"margin:0px=
 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex">I&#=
39;m attempting a three column select from two tables, where only a<br>
single column from each of the tables matters.<br>
<br>
t1.date and t2.time are both timestamptz.<br>
<br>
I want the three columns to be:<br>
<br>
t1.date::date<br>
<br>
t1.date - lag(t1.date,1) over (order by date asc) days,<br>
<br>
and count(t2.time) from the interval lag(t1.date,1) and t1.date.<br>
<br>
but that syntax of course fails do to the placements I&#39;ve tried for tha=
e<br>
between.<br>
<br>
I tried a sub-query but got what looked like an outer join.<br>
<br>
I want exactly count(*) from t1 rows in the result.<br>
<br>
What trick am I missing?<br><br></blockquote><div>I&#39;m a little confused=
 by your SQL, which appears to be incomplete? Could you give some code to c=
reate a simple table, populate it with a few sample rows, and then a full S=
QL query of what you are trying to accomplish? Also include what you get ba=
ck from your query and what you wish you were getting back, in terms of res=
ult sets..</div><div><br></div><div>The main thing I&#39;m missing is how t=
1 and t2 are joined.. I can&#39;t see that, so it&#39;s hard to understand =
why your query is not giving you the results you want.</div><div><br></div>=
<div>Best,</div><div>Steve</div></div></div>

--000000000000bcd104061511220d--