Re: Adding AVG to a JOIN

On Mon, Apr 23, 2018 at 12:47 PM, Alexander Farber <
alexander(dot)farber(at)gmail(dot)com> wrote:

> 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,
>
> And I don't understand why adding a CTE has caused it, because without the
> CTE the GROUP BY u.elo was not required...
>
>
​Adding "AVG(c.played - c.prev_played)" directly to the top-level select
statement​ column list is what turned it into a "GROUP BY" query. When you
embedded the "AVG(score)" in a subquery the GROUP BY was limited to just
that subquery, and it had no other columns besides the aggregate and so
didn't require a GROUP BY clause.

David J.