Re: Adding AVG to a JOIN - Mailing list pgsql-general

From Alexander Farber
Subject Re: Adding AVG to a JOIN
Date
Msg-id CAADeyWinpG6vWYYARBtONNL=VHBTVn-ocKcdZDDMvxywFVtoXQ@mail.gmail.com
Whole thread Raw
In response to Re: Adding AVG to a JOIN  ("Daniel Verite" <daniel@manitou-mail.org>)
Responses Re: Adding AVG to a JOIN
List pgsql-general
Thank you, Daniel -

On Mon, Apr 23, 2018 at 12:15 PM, Daniel Verite <daniel@manitou-mail.org> wrote:

You may use a correlated subquery in the SELECT clause,
like this:

 SELECT
       u.elo,
       u.uid,
       (SELECT AVG(score) FROM words_moves WHERE uid=u.uid),
       s.given,
       s.photo

this has worked great for me:

               SELECT
                        u.elo,
                        (SELECT ROUND(AVG(score), 1) FROM words_moves WHERE uid = u.uid) AS score,
                        s.given,
                        s.photo
                FROM words_users u
                JOIN words_social s USING (uid)
                WHERE u.elo > 1500
                -- take the most recent record from words_social
                AND NOT EXISTS (SELECT 1
                                FROM words_social x
                                WHERE s.uid = x.uid
                                AND x.stamp > s.stamp)
                -- only show players who where active in the last week
                AND EXISTS (SELECT 1
                            FROM words_moves
                            WHERE played > CURRENT_TIMESTAMP - INTERVAL '1 week'
                            AND action IN ('play', 'skip', 'swap', 'resign'))
                ORDER BY u.elo DESC
                LIMIT 10

Is that what is called LATERAL JOIN?

Regards
Alex

pgsql-general by date:

Previous
From: "Daniel Verite"
Date:
Subject: Re: Adding AVG to a JOIN
Next
From: Igor Neyman
Date:
Subject: RE: Strange error in Windows 10 Pro