Counting booleans in GROUP BY sections

Alexander Farber <[email protected]>
Newsgroups gmane.comp.db.postgresql.general
Message-ID <CAADeyWhQTjAFfE-ZhgpJL9HJRw3HJdd1bGUPOuyQUBGpHbrRGQ@mail.gmail.com>
Good evening,

I am trying to count the booleans per each GROUP BY section by the
following stored function:

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,
        COUNT(NULLIF(puzzle, FALSE)), -- this only returns 0 or 1, why?
        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
    GROUP BY label, puzzle, mid, secret, gid, score
    ORDER BY played DESC

$func$ LANGUAGE sql STABLE;

But when I run it, I only get 0 or 1 in the out_count column:

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  |         0 | f          | 1326876 |
e4928d3c34f50b8e6eabf7bad5b932fe |   46007 |        28
 Nov 2018  |         0 | f          | 1324466 |
6228ba509a7124f485feb5c1acbb6b68 |   45922 |        26
 Nov 2018  |         0 | f          | 1322050 |
b67b091d383678de392bf7370c735cab |   45877 |        34
 Nov 2018  |         0 | f          | 1320017 |
35f03b0c7159cec070c00aa80359fd42 |   44255 |       120
 Nov 2018  |         0 | f          | 1319160 |
83df42f7ad398bbb060fc02ddfdc62c0 |   45031 |        95
.....
 May 2018  |         0 | f          |  264251 |
2fff1154962966b16a2996387e30ae7f |   10946 |        99
 May 2018  |         1 | t          |  257620 |
645613db6ea40695dc967d8090ab3246 |   12713 |        93
 May 2018  |         0 | f          |  245792 |
bb75bfd9cb443ff541b199d893c68117 |   12359 |        24
 May 2018  |         1 | t          |  243265 |
d899a5d642ccd96d931194f48ef56d53 |   11950 |       123
 May 2018  |         0 | f          |  231953 |
ad53b5b2c0d4cced3d50e8b44ad53e55 |   11910 |        32

- while I was hoping to get 2 for the "May 2018" section.

What am I doing wrong please, why don't the values add up? Below is the
table desc:

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

Thank you
Alex
lmpx.com only provides a reader for public news (NNTP) servers. It is not affiliated with the servers or forums shown here and is not responsible for the content of articles, which is written by their respective authors.