Re: Counting booleans in GROUP BY sections

My context is that I have a table of player moves with PK mid (aka "move
id").

And I am able to find "interesting" moves by the high score or all 7 letter
tiles used.

But I do some human reviewing and set a "puzzle" boolean for truly
interesting moves.

For the reviewing tool I would like to display headers: a "Mon YYYY" plus
the number of true puzzles per section.

Thanks to David's hint the following seems to work even though I wonder if
it is the most optimal way to call TO_CHAR twice:

CREATE OR REPLACE FUNCTION words_list_puzzles(
                in_start interval,
                in_end interval
        ) RETURNS TABLE (
                out_label  text,
                out_count  bigint,
                out_puzzle boolean,
                out_mid    bigint,
                out_secret text,
                out_gid    integer,
                out_score  integer
        ) AS
$func$
    SELECT
        TO_CHAR(played, 'Mon YYYY') AS label,
                                                  -- used for header
        COUNT(NULLIF(puzzle, FALSE)) OVER (PARTITION BY TO_CHAR(played,
'Mon YYYY')), --used for header
        puzzle,
        mid,
        MD5(mid || 'my little secret') AS secret,
        gid,
        score
    FROM words_moves
    WHERE action = 'play'
    AND LENGTH(hand) = 7
    AND (LENGTH(letters) = 7 OR score > 90)
    AND played > CURRENT_TIMESTAMP - in_start
    AND played < CURRENT_TIMESTAMP - in_end
    ORDER BY played DESC
$func$ LANGUAGE sql STABLE;

Regards
Alex

P.S: Below is my table description again and the output of the above
function:

words_ru=> \d words_moves
                                      Table "public.words_moves"
 Column  |           Type           | Collation | Nullable |
  Default
---------+--------------------------+-----------+----------+------------------------------------------
 mid     | bigint                   |           | not null |
nextval('words_moves_mid_seq'::regclass)
 action  | text                     |           | not null |
 gid     | integer                  |           | not null |
 uid     | integer                  |           | not null |
 played  | timestamp with time zone |           | not null |
 tiles   | jsonb                    |           |          |
 score   | integer                  |           |          |
 letters | text                     |           |          |
 hand    | text                     |           |          |
 puzzle  | boolean                  |           | not null | false
Indexes:
    "words_moves_pkey" PRIMARY KEY, btree (mid)
    "words_moves_gid_played_idx" btree (gid, played DESC)
    "words_moves_uid_action_played_idx" btree (uid, action, played)
    "words_moves_uid_idx" btree (uid)
Check constraints:
    "words_moves_score_check" CHECK (score >= 0)
Foreign-key constraints:
    "words_moves_gid_fkey" FOREIGN KEY (gid) REFERENCES words_games(gid) ON
DELETE CASCADE
    "words_moves_uid_fkey" FOREIGN KEY (uid) REFERENCES words_users(uid) ON
DELETE CASCADE
Referenced by:
    TABLE "words_scores" CONSTRAINT "words_scores_mid_fkey" FOREIGN KEY
(mid) REFERENCES words_moves(mid) ON DELETE CASCADE

words_ru=> select * from words_list_puzzles(interval '2 year', interval '1
year');
 out_label | out_count | out_puzzle | out_mid |            out_secret
     | out_gid | out_score
-----------+-----------+------------+---------+----------------------------------+---------+-----------
 Nov 2018  |         1 | f          | 1331343 |
78467b5f3bde3d3f2291cf539c949f79 |   46134 |        28
 Nov 2018  |         1 | f          | 1326876 |
e4928d3c34f50b8e6eabf7bad5b932fe |   46007 |        28
 Nov 2018  |         1 | f          | 1324466 |
6228ba509a7124f485feb5c1acbb6b68 |   45922 |        26
 Nov 2018  |         1 | f          | 1322050 |
b67b091d383678de392bf7370c735cab |   45877 |        34
 Nov 2018  |         1 | f          | 1320017 |
35f03b0c7159cec070c00aa80359fd42 |   44255 |       120
.....
 May 2018  |         3 | f          |   95114 |
e7e8bab64fab20f6fec229319e2bab40 |    7056 |        28
 May 2018  |         3 | f          |   88304 |
161c0638dede80f830a36efa6f428dee |    6767 |        40
 May 2018  |         3 | f          |   86180 |
4d47a65263331cf4e2d2956886b6a72f |    6706 |        26
 May 2018  |         3 | f          |   85736 |
debb1efd673c91947a8aa7f38be4217c |    6680 |        28
 May 2018  |         3 | f          |   82522 |
e55ec68a5a5dacc2bc463e397198cb1c |    6550 |        27
 Apr 2018  |         0 | f          |   78406 |
f5d264ccfe94aaccd90ce6c019716d4d |    5702 |        58
 Apr 2018  |         0 | f          |   77461 |
404886e913b698596f9cf3648ddf6fa4 |    1048 |        26
(415 rows)