CODEWITHSUNDEEP
sqlpostgresql
Arya
Arya

Reputation: 8985

SQL query to count based on an id and another boolean column

I have the following table that tracks chat message logs

CREATE TABLE public.perm_message_log
(
    id bigint NOT NULL DEFAULT nextval('perm_message_log_id_seq'::regclass),
    message text,
    from_id text,
    to_id text,
    match_id text,
    unix_timestamp bigint,
    own_account boolean,
    reply_batch boolean DEFAULT false,
    insert_time timestamp with time zone DEFAULT now(),
    account_id bigint,
    match_pk bigint
)

each conversation has the same match_id I want to construct a SQL query that would return the match_ids only if there are less than two reply_batch = true for the messages with the same match_id

I hope I was able to write it in a clear way enough.

Each row that contains a message has the column reply_batch which is set to true or false. I want the query to return all the match_ids that have less than two "true" booleans for the reply_batch column.

EDIT @TimBiegeleisen

Thanks for your answer, what if I were to delete the rows your answer returns in another table? I came up with the query below but it's wrong.

delete from already_matched where already_matched.userid = (
WITH cte AS (
    SELECT match_id
    FROM public.perm_message_log
    GROUP BY match_id
    HAVING SUM(CASE WHEN reply_batch THEN 1 ELSE 0 END) = 0
)
SELECT  t1.from_id
FROM public.perm_message_log t1
INNER JOIN cte t2
    ON t1.match_id = t2.match_id
WHERE NOT t1.own_account)

Upvotes: 0

Views: 168

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521609

You can try aggregating your table by the match_id, counting the number of times which reply_batch be true for that match_id. If this count is less than two, then retain that match_id in the result set.

WITH cte AS (
    SELECT match_id
    FROM public.perm_message_log
    GROUP BY match_id
    HAVING SUM(CASE WHEN reply_batch THEN 1 ELSE 0 END) < 2
)
SELECT t1.match_id,
       t1.from_id
FROM public.perm_message_log t1
INNER JOIN cte t2
    ON t1.match_id = t2.match_id
WHERE NOT t1.own_account

Edit:

If you want to use the set of match_id returned by the CTE to delete certains rows in another table, you can try:

DELETE
FROM already_matched
WHERE match_id IN (SELECT t.match_id FROM cte t)

Upvotes: 1

Related Questions