Delete Report with months reset

Question

Delete Report with months reset

Navigation

#1 by (1 votes)
#2 by (0 votes)
#3 by (0 votes)

1

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

I would like to do different would like it to also show the months that did not have deposit I would like to bring zeroed according to the image below my bank is Postgres.

How to Bring Today

HowIWouldLiketoBring

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;

mysql sql query postgresql select

asked by anonymous 08.11.2016 / 16:19

3 answers

Why is my String names accepting integers instead of letters? prolog recurrence problem

score 1 · 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;

score 0 · Answer 2

In order to display the 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 it is nvl ([your field] , 0)

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

nvl(fi.valor,0)

score 0 · Answer 3

You can also always use a CTE and a CASE (in postgres I always prefer to use CTE's to improve reading and split the querys

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