Hello Charles and other, please excuse my stupidity, but -
On Tue, Sep 6, 2016 at 2:52 PM, Charles Clavadetscher <
clavadetsc...@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
