Re: Counting booleans in GROUP BY sections - Mailing list pgsql-general

On 11/29/19 8:38 AM, Alexander Farber wrote:
>
> 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?

If I am following it is because you have mid in GROUP BY and mid is a
PK. Since mid will always be unique you will have at most on row per group.

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

if I remove GROUP BY mid, then I get the error:

ERROR:  42803: column "words_moves.mid" must appear in the GROUP BY clause or be used in an aggregate function
LINE 18:         mid,
                 ^
LOCATION:  check_ungrouped_columns_walker, parse_agg.c:1369

Regards
Alex