Problem with full Outer Join filtering with Where

Question

Friends,

I have a database with the following tables:

guys

id | nome
1  | Duplo
2  | Triplo

availability

    data   | tipo | ocupado | manutencao | referencia
01/03/2018 | 1    | 1       | 0          | 54684
01/03/2018 | 1    | 0       | 1          | 4525
01/03/2018 | 1    | 1       | 0          | 54686

I’d like to make a query to return the following data:

   data    | nome   | ocupado | manutencao 
01/03/2018 | Duplo  | 2       | 1
01/03/2018 | Triplo | 0       | 0

I made the query below only that did not return me the table data guys, found that only does not return the result I want when I filter by dates.

SELECT
d.data,
t.nome,
COALESCE(SUM(ocupado),0) ocupado,
COALESCE(SUM(manutencao),0) manutencao
FROM disponibilidade d
FULL OUTER JOIN tipos t ON d.tipo = t.id
WHERE d.data between '03-01-2018' AND '03-05-2018'
GROUP BY d.data, t.nome

I made this fiddle to check the database structure.

How to use Join in a way that makes the date filter with WHERE and return me the result I need?

Answer 1

The way it is being done will not bring any name record Triple since it is not present in the availability table as it is being used the FULL OUTER JOIN, recommend to perform the survey starting at the type table and using the LEFT JOIN as follows:

SELECT 
    D.DATA, 
    T.NOME,
    COALESCE(SUM(OCUPADO),0) OCUPADO,
    COALESCE(SUM(MANUTENCAO),0) MANUTENCAO
FROM 
    TIPOS T
LEFT JOIN 
    DISPONIBILIDADE D ON D.TIPO = T.ID
GROUP BY 
    D.DATA, T.NOME