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

Hello Charles and other, please excuse my stupidity, but -

On Tue, Sep 6, 2016 at 2:52 PM, Charles Clavadetscher <
clavadetscher(at)swisspug(dot)org> wrote:

>
> You must group by played, as the message suggests. You are implicitly
> selecting the column through order by, although you don't have it in the
> list of selected columns.
>
>

Here I have 7 "skip" events for gid=3 ("game id") in the table:

words=> select mid, action, gid, uid from words_moves order by played desc;
mid | action | gid | uid
-----+--------+-----+-----
15 | skip | 3 | 1
14 | skip | 3 | 2
13 | skip | 3 | 1
12 | skip | 3 | 2
11 | skip | 3 | 1
10 | skip | 3 | 2
9 | skip | 3 | 1
6 | play | 3 | 2
5 | play | 4 | 1
3 | swap | 3 | 1
2 | play | 2 | 1
1 | play | 1 | 1
(12 rows)

And then I try the suggestion I got in this mailing list:

words=> SELECT SUM(CASE WHEN action='skip' THEN 1 ELSE 0 END)
words-> FROM words_moves
words-> WHERE gid = 3
words-> GROUP BY played
words-> ORDER BY played DESC
words-> LIMIT 6;
sum
-----
1
1
1
1
1
1
(6 rows)

I guess I need ASC in the last statement, but main problem is how to get
the total sum...

Regards
Alex