Select all months of the year in postgres

Question

Salve galera,

I have the following select:

select sum(valor) valor, extract (month from datahorainicio) from agendamento where extract (year from datahorainicio)= 2020 and status='CONCLUIDO' and id_barbearia=1 group by extract(month from datahorainicio)

He returns me the sum of the amounts for services rendered in a month.

I would like to know how to return every month of the year in select, even if there is a month in which there is no value. I need it so I can fill a chart on the front with the months and stuff.

Answer 1

The generate_series function together with LEFT OUTER JOIN can be very useful:

SELECT foo.data, COALESCE(SUM(valor), 0) valor FROM generate_series('2020-01-01'::date, '2020-31-12'::date, '1 month') AS foo(data)
LEFT OUTER JOIN agendamento 
WHERE data_trunc(datahorainicio, 'day')= foo.data AND status='CONCLUIDO' AND id_barbearia=1 
GROUP BY foo.data;

Answer 2

I did it exactly this week to calculate hours worked in the month of employees!! Just use a GROUP BY!! Put an alias for your "Extract(Month from datahorainicio)" as for example "Extract(Month from datahorainicio) as Month", then at the end you put GROUP BY Month. It is easier to visualize with the use of alias. The data in this column will be in numeral, then you work in your back end to turn in month or set straight in the bank (will stay in English). But it works without error with GROUP BY!!