Re: Adding AVG to a JOIN

Good evening,

On Mon, Apr 23, 2018 at 12:56 PM, Alexander Farber <
alexander(dot)farber(at)gmail(dot)com> wrote:
>
> On Mon, Apr 23, 2018 at 12:15 PM, Daniel Verite <daniel(at)manitou-mail(dot)org>
> wrote:
>
>>
>> You may use a correlated subquery in the SELECT clause,
>>
>
> 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
>

thank you all for helping me in adding an average score per move to my SQL
JOIN.

Of course I would like to add yet another statistic and now am trying to
add the average time per move to the same query, by prepending it a CTE
with LAG():

WITH cte AS (
SELECT
m.gid,
m.uid,
m.played,
LAG(m.played) OVER (PARTITION BY m.gid
ORDER BY played) AS prev_played
FROM words_moves m
JOIN words_games g ON (m.gid = g.gid AND m.uid in
(g.player1, g.player2))
WHERE m.played > CURRENT_TIMESTAMP - interval '1
month'
)
SELECT
u.elo,
AVG(c.played - c.prev_played) AS avg_time_per_move,
(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)
JOIN cte c 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;

but this fails with

ERROR: 42803: column "u.elo" must appear in the GROUP BY clause or be used
in an aggregate function
LINE 15: u.elo,
^

And I don't understand why adding a CTE has caused it, because without the
CTE the GROUP BY u.elo was not required...

Regards
Alex