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@swisspug.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