What did I miss in my request? Is he faithful?

Question

SQL

Good evening!
There are rows like this in the table: ( SQLite3 )

consultant => 000010, read => 0
consultant => 000010, read => 0
consultant => 000010, read => 0
consultant => 000010, read => 1
consultant => 000011, read => 1
consultant => 000019, read => 0
consultant => 000034, read => 1

The given request displays what I need! It counts the total number of each consultant and outputs their number with the parameter read=0

SELECT T1.consultant, T1.count, T2.unread
FROM
  (SELECT consultant, COUNT(consultant) AS count
   FROM Messages GROUP BY consultant) T1,
   
  (SELECT consultant, COUNT(consultant) AS unread
   FROM Messages WHERE read = '0' GROUP BY consultant) T2
WHERE T1.consultant = T2.consultant;

I get this response from a request:

consultant => 000010, count => 4, unread => 3
consultant => 000019, count => 1, unread => 1

But correct me, it doesn't output all unique consultant s, i.e. those that with read=1 don't output.. help..
In theory, the answer should be like this:

consultant => 000010, count => 4, unread => 3
consultant => 000011, count => 1, unread => 0
consultant => 000019, count => 1, unread => 1
consultant => 000034, count => 1, unread => 0

Answer 1

can you change your request a bit?

SELECT 
    T1.consultant, count(T1.consultant) AS count, T2.unread
FROM 
    Messages T1
LEFT JOIN 
    (SELECT consultant, count(*) AS unread  FROM Messages WHERE read = '0' GROUP BY consultant) T2
ON 
    T1.consultant = T2.consultant
GROUP BY 
    T1.consultant

I didn't check the code itself, but the result should be something like this:

consultant => 000010, count => 4, unread => 3
consultant => 000011, count => 1, unread => null
consultant => 000019, count => 1, unread => 1
consultant => 000034, count => 1, unread => null

Answer 2