Quick Links

Adding AVG to a JOIN

From:	Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
To:	pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject:	Adding AVG to a JOIN
Date:	2018-04-23 08:58:45
Message-ID:	CAADeyWiM21RZVsc4McqydbRxE4sNvUp-xATehfkTqjcrqG8WSA@mail.gmail.com
Views:	Raw Message \| Whole Thread \| Download mbox \| Resend email
Thread:
Lists:	pgsql-general

Hello,

in PostgreSQL 10.3 I run the following query to find top 10 players with
the best ELO rating:

# SELECT
u.elo,
u.uid,
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 (storing user details from
social networks)
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;

The above query works well and quick, but I need to add the average score
per move information to it.

Here is such a query for the best player

# SELECT AVG(score) FROM words_moves WHERE uid = 1201;
avg
---------------------
18.4803525523319868

However I am not sure, how to "marry" the 2 queries?

I have tried to add words_moves through another JOIN, but that does not
work:

# SELECT
u.elo,
u.uid,
AVG(m.score), -- how to add the player average score?
s.given,
s.photo
FROM words_users u
JOIN words_social s USING (uid)
JOIN words_moves m USING (uid)
WHERE u.elo > 1500

AND NOT EXISTS (SELECT 1
FROM words_social x
WHERE s.uid = x.uid
AND x.stamp > s.stamp)

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
;
ERROR: 42803: column "u.elo" must appear in the GROUP BY clause or be used
in an aggregate function
LINE 2: u.elo,
^

Please give me some hints, how to approach this.

Thank you!
Alex

P.S: Below are the 3 tables referenced above:

# \d words_users
Table "public.words_users"
Column | Type | Collation | Nullable |
Default
---------------+--------------------------+-----------+----------+------------------------------------------
uid | integer | | not null |
nextval('words_users_uid_seq'::regclass)
created | timestamp with time zone | | not null |
visited | timestamp with time zone | | not null |
ip | inet | | not null |
fcm | text | | |
apns | text | | |
adm | text | | |
motto | text | | |
vip_until | timestamp with time zone | | |
grand_until | timestamp with time zone | | |
banned_until | timestamp with time zone | | |
banned_reason | text | | |
elo | integer | | not null |
medals | integer | | not null |
coins | integer | | not null |
Indexes:
"words_users_pkey" PRIMARY KEY, btree (uid)
Check constraints:
"words_users_banned_reason_check" CHECK (length(banned_reason) > 0)
"words_users_elo_check" CHECK (elo >= 0)
"words_users_medals_check" CHECK (medals >= 0)
Referenced by:
TABLE "words_chat" CONSTRAINT "words_chat_uid_fkey" FOREIGN KEY (uid)
REFERENCES words_users(uid) ON DELETE CASCADE
TABLE "words_games" CONSTRAINT "words_games_player1_fkey" FOREIGN KEY
(player1) REFERENCES words_users(uid) ON DELETE CASCADE
TABLE "words_games" CONSTRAINT "words_games_player2_fkey" FOREIGN KEY
(player2) REFERENCES words_users(uid) ON DELETE CASCADE
TABLE "words_moves" CONSTRAINT "words_moves_uid_fkey" FOREIGN KEY (uid)
REFERENCES words_users(uid) ON DELETE CASCADE
TABLE "words_reviews" CONSTRAINT "words_reviews_author_fkey" FOREIGN
KEY (author) REFERENCES words_users(uid) ON DELETE CASCADE
TABLE "words_reviews" CONSTRAINT "words_reviews_uid_fkey" FOREIGN KEY
(uid) REFERENCES words_users(uid) ON DELETE CASCADE
TABLE "words_scores" CONSTRAINT "words_scores_uid_fkey" FOREIGN KEY
(uid) REFERENCES words_users(uid) ON DELETE CASCADE
TABLE "words_social" CONSTRAINT "words_social_uid_fkey" FOREIGN KEY
(uid) REFERENCES words_users(uid) ON DELETE CASCADE

Responses

Re: Adding AVG to a JOIN at 2018-04-23 10:15:14 from Daniel Verite

Browse pgsql-general by date

	From	Date	Subject
Next Message	Daniel Verite	2018-04-23 10:15:14	Re: Adding AVG to a JOIN
Previous Message	Andres Freund	2018-04-23 00:05:30	Re: Postgres and fsync