public inbox for [email protected]  

help / color / mirror / Atom feed

From: James Cloos <[email protected]>
To: [email protected]
Subject: help with a particular multi-table query
Date: Mon, 01 Apr 2024 18:03:26 -0400
Message-ID: <[email protected]> (raw)

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 <[email protected]>
            OpenPGP: https://jhcloos.com/0x997A9F17ED7DAEA6.asc

view thread (3+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: help with a particular multi-table query
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox