Re: Check if there 6 last records of same type without gaps

No, I am sorry - for struggling with probably basic questions, but without
GROUP BY I get another error:

org.postgresql.util.PSQLException: ERROR: column "words_moves.played" must
appear in the GROUP BY clause or be used in an aggregate function|
Where: PL/pgSQL function words_skip_game(integer,integer) line 27 at SQL
statement

On Tue, Sep 6, 2016 at 2:30 PM, Sándor Daku <daku(dot)sandor(at)gmail(dot)com> wrote:

> On 6 September 2016 at 14:23, Alexander Farber <alexander(dot)farber(at)gmail(dot)com
> > wrote:
>
>>
>> On Tue, Sep 6, 2016 at 1:30 PM, Sándor Daku <daku(dot)sandor(at)gmail(dot)com>
>> wrote:
>>
>>>
>>> Get the last 6 record and
>>>
>>> 1. ... action='SKIP' as isskip ... then you can group on and count the
>>> skip moves. If there is 6 of them the game ends.
>>>
>>> 2. ... sum(case when action='SKIP' then 1 else 0 end) ... If the result
>>> is 6 the game ends
>>>
>>>
>> SELECT SUM(CASE WHEN action='skip' THEN 1 ELSE 0 END)
>> FROM words_moves
>> GROUP BY action
>> ORDER BY played DESC
>> LIMIT 6
>> INTO _sum;
>>
>> RAISE NOTICE '_sum = %', _sum;
>>
>> IF _sum = 6 THEN
>> _finished = CURRENT_TIMESTAMP;
>> END IF;
>>
>> but get the error -
>>
>> org.postgresql.util.PSQLException: ERROR:
>> column "words_moves.played" must appear in the GROUP BY clause or be used
>> in an aggregate function|
>> Where: PL/pgSQL function words_skip_game(integer,integer) line 27 at SQL
>> statement
>>
>

> P.S: Here is the table in question
>>
>> Table "public.words_moves"
>> Column | Type | Modifiers
>> --------+--------------------------+------------------------
>> -----------------------------------
>> mid | integer | not null default
>> nextval('words_moves_mid_seq'::regclass)
>> action | words_action | not null
>> gid | integer | not null
>> uid | integer | not null
>> played | timestamp with time zone | not null
>> tiles | jsonb |
>> score | integer |
>> Indexes:
>> "words_moves_pkey" PRIMARY KEY, btree (mid)
>> Check constraints:
>> "words_moves_score_check" CHECK (score > 0)
>> Foreign-key constraints:
>> "words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid)
>> ON DELETE CASCADE
>> "words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid)
>> ON DELETE CASCADE
>> Referenced by:
>> TABLE "words_games" CONSTRAINT "words_mid_fk" FOREIGN KEY (mid)
>> REFERENCES words_moves(mid)
>> TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY
>> (mid) REFERENCES words_moves(mid) ON DELETE CASCADE
>>
>>
> Sry! I wasn't clear enough.
>
> Those are two separate solutions. Pick one!
>
> In this case you don't need the group by
>
> SELECT SUM(CASE WHEN action='skip' THEN 1 ELSE 0 END)
> FROM words_moves
> ORDER BY played DESC
> LIMIT 6
> INTO _sum
>
>