public inbox for [email protected]  

help / color / mirror / Atom feed

From: Steve Midgley <[email protected]>
To: James Cloos <[email protected]>
Cc: [email protected]
Subject: Re: help with a particular multi-table query
Date: Mon, 1 Apr 2024 16:13:09 -0700
Message-ID: <CAJexoS+gyE6-GqRqwZ+e79dX0JSi8KOYKLFiaJNVC1L0mNMhTA@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>

On Mon, Apr 1, 2024 at 3:03 PM James Cloos <[email protected]> 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

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], [email protected]
  Subject: Re: help with a particular multi-table query
  In-Reply-To: <CAJexoS+gyE6-GqRqwZ+e79dX0JSi8KOYKLFiaJNVC1L0mNMhTA@mail.gmail.com>

* 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