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

On 6 September 2016 at 15:19, Alexander Farber <alexander(dot)farber(at)gmail(dot)com>
wrote:

> 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
>
>
My bad. I didn't pay attention.

Of course you need the played field you relied on it in the order by
clause. You can use the result of a select in a from clause of another
select.

SELECT SUM(skips) from (SELECT CASE WHEN action='skip' THEN 1 ELSE 0 END as
skips, played
FROM words_moves
WHERE gid = 3
ORDER BY played DESC
LIMIT 6) as skipscount;

And now I really hope, I didn't miss something important again!

Regards,
Sándor