Re: Counting booleans in GROUP BY sections

Thank you Adrian, but -

On Fri, Nov 29, 2019 at 6:45 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> 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