SQL query return reset value where COUNT finds null

Question

Hello, I have a table of projects that contains status, responsible and other columns. I’m doing the following consultation,

  SELECT status,COUNT(status) AS Total 
  FROM projetos 
  WHERE responsavel='Teste' 
  GROUP BY status ORDER BY status;

Then returns only the status that that responsavel has and I would need it to return all status but with the COUNT(status) with zero value.

This way the table comes this way:

+-----------+-------+
| status    | total |
+-----------+-------+
| Cancelado |     1 |
+-----------+-------+

And I need it to come from the following:

+-------------+-------+
| status      | total |
+-------------+-------+
| Cancelado   |     1 |
| Finalizado  |     0 |
| Em processo |     0 |
| Parado      |     0 |
| Atrasado    |     0 |
+-------------+-------+

The responsible named test has only one project in which the status is as Canceled.

Answer 1

Try the coalesce function that serves exactly for this:

COALESCE(COUNT(status), 0) AS Total

================

If you have a possible status table then use a LEFT OUTER JOIN:

SELECT tabela_de_status.status, COALESCE(COUNT(projetos.status), 0) AS Total 
  FROM tabela_de_status LEFT OUTER JOIN ON (tabela_de_status. status = projetos.status)
  WHERE responsavel='$Nome_de_Algum_responsavel' 
  GROUP BY tabela_de_status.status ORDER BY tabela_de_status.status;

===============================

To consider all existing status in the projects table:

SELECT tabela_de_status.status, COALESCE(COUNT(projetos.status), 0) AS Total 
  FROM (SELECT DISTINCT status FROM projetos) AS tabela_de_status LEFT OUTER JOIN ON (tabela_de_status. status = projetos.status)
  WHERE responsavel='$Nome_de_Algum_responsavel' 
  GROUP BY tabela_de_status.status ORDER BY tabela_de_status.status;

But I would say that your database is not well structured.