How to compare COUNT for equality with 0 in a query?

Question

MySQL

There is such a request

SELECT dialogs.id_dialog AS id, clients.name AS name, COUNT( messages.status) AS qt 
FROM dialogs INNER JOIN clients ON dialogs.id_client = clients.id_client INNER JOIN 
messages ON dialogs.id_dialog = messages.id_dialog WHERE dialogs.id_manager = '1' AND 
messages.status = '0' GROUP BY dialogs.id_dialog

How to put this in it

IF (COUNT( messages.status) == '0') COUNT( messages.status) = 0

That is, if the value of COUNT = 0, still display the record, but display 0 in the qt column.

Answer 1

Here is the solution to my problem

SELECT dialogs.id_dialog AS id, clients.name AS name, SUM(case when messages.status = 0 then 1 else 0 end) AS qt 
FROM dialogs INNER JOIN clients ON dialogs.id_client = clients.id_client INNER JOIN 
messages ON dialogs.id_dialog = messages.id_dialog WHERE dialogs.id_manager = '1' GROUP BY dialogs.id_dialog

Now returns the number of messages with status 0.

Answer 2

HAVING
as an example tftf.ru/stati/mysql/having or in google/ docs

Answer 3

SELECT dialogs.id_dialog AS id, clients.name AS name, SUM(IF(messages.status = '0',0,1)) AS qt 
FROM dialogs INNER JOIN clients ON dialogs.id_client = clients.id_client INNER JOIN 
messages ON dialogs.id_dialog = messages.id_dialog WHERE dialogs.id_manager = '1' AND 
messages.status = '0' GROUP BY dialogs.id_dialog

this is done via SUM(IF()), but you have a WHERE constraint messages.status = '0', so this query will always return qt =0