Thank you, the following seems to have worked -
On Tue, Jan 15, 2019 at 8:49 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:
>
> UPDATE users
> SET avg_time = diffs.average_time_for_the_grouped_by_user
> FROM diffs
> WHERE users.uid = diffs.uid --< the missing "where" I commented about
e
On Tue, Jan 15, 2019 at 12:42 PM Alexander Farber
wrote:
>
> Last question please - how to run the query for all users?
>
> I know I could use the FOR-loop from PL/PgSQL, but is there also a pure SQL
> way?
>
> How to refer to the outside "uid" from inside the CTE in the query below?
>
> WITH dif
Last question please - how to run the query for all users?
I know I could use the FOR-loop from PL/PgSQL, but is there also a pure SQL
way?
How to refer to the outside "uid" from inside the CTE in the query below?
WITH diffs AS (
SELECT
gid,
uid,
played - LAG(played) OVER(PAR
And I should better change the avg_time column from TEXT to TIMESTAMPTZ
(and use TO_CHAR on it later down the road) so that I can compare my players
Regards
Alex
>
Ahh, the subqueries -
On Tue, Jan 15, 2019 at 5:59 PM David G. Johnston <
david.g.johns...@gmail.com> wrote:
> On Tue, Jan 15, 2019 at 9:52 AM Alexander Farber
> wrote:
> >> So calculate the average somewhere else, put the result in a column,
> >> and then reference that column in the SET clause
On Tue, Jan 15, 2019 at 9:52 AM Alexander Farber
wrote:
>> So calculate the average somewhere else, put the result in a column,
>> and then reference that column in the SET clause.
>>
>
> do you suggest to add a second CTE?
That would qualify as "somewhere else" - as would a simple subquery in FR
iffs;
> >
> > the syntax error is unfortunately printed by PostgreSQL 10:
> >
> > aggregate functions are not allowed in UPDATE
>
> So calculate the average somewhere else, put the result in a column,
> and then reference that column in the SET clause.
>
>
do you suggest to add a second CTE?
Regards
Alex
SET
> avg_time = TO_CHAR(AVG(diff), 'HH24:MI')
> FROM diffs;
>
> the syntax error is unfortunately printed by PostgreSQL 10:
>
> aggregate functions are not allowed in UPDATE
So calculate the average somewhere else, put the result in a column,
and then reference that
store it in the "avg_time" text
column of the users table?
When I am trying
WITH diffs AS (
SELECT
gid,
uid,
played - LAG(played) OVER(PARTITION BY gid ORDER BY played) AS diff
FROM moves
WHERE uid = 1
)
UPDATE users SET
avg_time = TO_CHAR(AVG(diff), 'HH24:MI')
FROM diffs;
the syntax error is unfortunately printed by PostgreSQL 10:
aggregate functions are not allowed in UPDATE
Regards
Alex
