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:
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!