From: James Cloos <cloos@jhcloos.com>
To: pgsql-sql@lists.postgresql.org
Subject: help with a particular multi-table query
User-Agent: Gnus/5.13 (Gnus v5.13)
Face: iVBORw0KGgoAAAANSUhEUgAAABAAAAAQAgMAAABinRfyAAAACVBMVEX///8ZGXBQKKnCrDQ3
 AAAAJElEQVQImWNgQAAXzwQg4SKASgAlXIEEiwsSIYBEcLaAtMEAADJnB+kKcKioAAAAAElFTkSu
 QmCC
Copyright: Copyright 2024 James Cloos
OpenPGP: 0x997A9F17ED7DAEA6;
 url=https://jhcloos.com/public_key/0x997A9F17ED7DAEA6.asc
OpenPGP-Fingerprint: E9E9 F828 61A4 6EA9 0F2B  63E7 997A 9F17 ED7D AEA6
Date: Mon, 01 Apr 2024 18:03:26 -0400
Message-ID: <m3frw468r5.fsf@nitrogen.jhcloos.org>
Lines: 26
MIME-Version: 1.0
Content-Type: text/plain
Archived-At: 
 <https://www.postgresql.org/message-id/m3frw468r5.fsf%40nitrogen.jhcloos.org>
Precedence: bulk

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?

-JimC
-- 
James Cloos <cloos@jhcloos.com>
            OpenPGP: https://jhcloos.com/0x997A9F17ED7DAEA6.asc