Take Report with zero months

Question

I have a query in which I take monthly deposited values it takes everything it has in the base and shows the months that were entered in the base.

I would like to do different I would like to show also the months that had no deposit I would like to bring zeroed as the image below my bank is the Postgres.

How It Brings Today

How Would You Like to Bring

Query

SELECT to_char(fi.data,'yyyy/mm') as Mês, count(fi.*) as valor 
  FROM financeiro fi 
  WHERE fi.status = '1' 
  GROUP BY Mês 
  ORDER BY Mês;

Answer 1

Make a outer join with generate_series:

select to_char(d.data,'yyyy/mm') as Mês, count(fi.*) as valor 
from
    financeiro fi
    right join
    generate_series(
        '2016-01-01'::date, '2016-12-01, '1 month'
    ) gs (d) on d = date_trunc('month', fi.data)
where fi.status = '1' 
group by mês 
order by mês;

Answer 2

You can also always use a CTE and a CASE (in postgres I always prefer to use CTE’s to improve reading and split kerys

WITH consulta AS (
SELECT to_char(fi.data,'yyyy/mm') as Mes, count(fi.*) as valor 
FROM financeiro fi 
WHERE fi.status = '1' 
GROUP BY Mês 
ORDER BY Mês;
)
SELECT mes, 
CASE WHEN valor IS NULL THEN '0'
ELSE valor
END
FROM consulta

Answer 3

To also display NULL values we need to use a function that replaces the null value with a desired value, in this case 0. Depending on the database you are using the syntax changes but in oracle is nvl([your field],0)

If your database is oracle try calling the value field like this:

nvl(fi.valor,0)