Count columns in a query with a group
I have the following table
id integer NOT NULL DEFAULT nextval('cl_id_seq'::regclass),
from_number character varying(30),
to_number character varying(30),
message text,
received_at timestamp with time zone,
sent_at timestamp with time zone,
reply_batch boolean DEFAULT false,
chat_id character varying(20),
insert_time timestamp with time zone DEFAULT now(),
own_account boolean DEFAULT false,
I have the following query that will return chat_ids in chains only if the last message (marked by the insert_time column) associated with the chat_id has an own_account column with false.
select chat_id from message_log
where own_account = 'f'
and insert_time in
(
select distinct max(insert_time) from message_log group by chat_id
)
The above SQL works fine, but it returns conversations without checking how many times true_all_account is used in the chat. I would like to add the ability to return chat_ids from conversations also depending on how many times own_account is true.
I have tried many different SQL queries but I cannot get it to work. Any help would be appreciated.
I hope I'm clear enough. if you confuse anything, please comment.
EDIT
I have uploaded data to SQL Fiddle here
http://sqlfiddle.com/#!15/1d7dc/2
If the next request is made, it will return the chat related messages.
select * from message_log where chat_id = '1412' order by insert_time
The last message does not belong to own_account and there are less than 3 own_account messages in the results, so the next request should return the chat_id to it, but it does not
select m.chat_id
from message_log m
inner join
(select chat_id, max(insert_time) as max_insert_time,
count(case when own_account='t' then 1 else 0 end) as true_count
from message_log
group by chat_id) as latest
on m.chat_id = latest.chat_id and
m.insert_time = latest.max_insert_time
where
m.own_account = 'f' and latest.true_count <= 3
EDIT 2
I created another sql script here with one entry
source to share
You can create a view that records the last insert times for all chats and then find out if that last record matches own_account='f'
select m.chat_id
from message_log m
inner join
(select chat_id, max(insert_time) as max_insert_time
from message_log
group by chat_id) as latest
on m.chat_id = latest.chat_id and
m.insert_time = latest.max_insert_time
where
m.own_account = 'f'
Extension to find chats where the last one is own_account = 'f' but has at least 3 own_account='t'
entries
select m.chat_id
from message_log m
inner join
(select chat_id, max(insert_time) as max_insert_time,
sum(case when own_account='t' then 1 else 0 end) as true_count
from message_log
group by chat_id) as latest
on m.chat_id = latest.chat_id and
m.insert_time = latest.max_insert_time
where
m.own_account = 'f' and latest.true_count >= 3
SQLFiddle is available at http://sqlfiddle.com/#!15/ee8c0/2
source to share