Re: Adding AVG to a JOIN

["David G. Johnston" <david.g.johnston@xxxxxxxxx>]

On Mon, Apr 23, 2018 at 12:47 PM, Alexander Farber <alexander.farber@xxxxxxxxx> 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.