On 6 September 2016 at 15:19, Alexander Farber <alexander.farber@xxxxxxxxx> wrote:
Hello Charles and other, please excuse my stupidity, but -On Tue, Sep 6, 2016 at 2:52 PM, Charles Clavadetscher <clavadetscher@xxxxxxxxxxxx> 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="" 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...RegardsAlex
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="" THEN 1 ELSE 0 END as skips, played
FROM words_movesWHERE 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
