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.