Re: help with a particular multi-table query

On Mon, Apr 1, 2024 at 3:03 PM James Cloos <cloos@jhcloos.com> wrote:

> 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