Counting booleans in GROUP BY sections - Mailing list pgsql-general
| From | Alexander Farber |
|---|---|
| Subject | Counting booleans in GROUP BY sections |
| Date | |
| Msg-id | CAADeyWhQTjAFfE-ZhgpJL9HJRw3HJdd1bGUPOuyQUBGpHbrRGQ@mail.gmail.com Whole thread Raw |
| Responses |
Re: Counting booleans in GROUP BY sections
(Adrian Klaver <adrian.klaver@aklaver.com>)
|
| List | pgsql-general |
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;
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
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
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
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
pgsql-general by date: