Month date in postgresql group with missing values

Question

first an example of my table:

id_object;time;value;status 1;2014-05-22 09:30:00;1234;1 1;2014-05-22 09:31:00;2341;2 1;2014-05-22 09:32:00;1234;1 ... 1;2014-06-01 00:00:00;4321;1 ... 

Now I need to count all the lines with status = 1 and id_object = 1 month, for example. this is my request:

 SELECT COUNT(*) FROM my_table WHERE id_object=1 AND status=1 AND extract(YEAR FROM time)=2014 GROUP BY extract(MONTH FROM time) 

The result for this example:

 2 1 

2 for may and 1 for June, but I need a conclusion with all 12 months, as well as months without data. for this example i need this output:

 0 0 0 0 2 1 0 0 0 0 0 0 

Thanks for reference.

Answer 1

you can use the generate_series() function as follows:

 select g.month, count(m) from generate_series(1, 12) as g(month) left outer join my_table as m on m.id_object = 1 and m.status = 1 and extract(year from m.time) = 2014 and extract(month from m.time) = g.month group by g.month order by g.month 

demo version of sql

Answer 2

Instead of comparing with the extracted value you need to use a range table. Something like this:

 month startOfMonth nextMonth 1 '2014-01-01' '2014-02-01' 2 '2014-02-01' '2014-03-01' ...... 12 '2014-12-01' '2015-01-01' 

As in @Roman's answer, we start with generate_series() , this time using it to create a range table:

 WITH Month_Range AS (SELECT EXTRACT(MONTH FROM month) AS month, month AS startOfMonth, month + INTERVAL '1 MONTH' AS nextMonth FROM generate_series(CAST('2014-01-01' AS DATE), CAST('2014-12-01' AS DATE), INTERVAL '1 month') AS mr(month)) SELECT Month_Range.month, COUNT(My_Table) FROM Month_Range LEFT JOIN My_Table ON My_Table.time >= Month_Range.startOfMonth AND My_Table.time < Month_Range.nextMonth AND my_table.id_object = 1 AND my_table.status = 1 GROUP BY Month_Range.month ORDER BY Month_Range.month 

(As a side note, I'm now annoyed at how PostgreSQL handles intervals)

SQL Fiddle Demo

Using a range will allow you to use any index, including My_Table.time (although not if the index was built on an EXTRACT ed column.

EDIT:

A modified request to take advantage of the fact that generate_series(...) will also handle dates / time series.

Answer 3

generate_series can generate a series of time series

 select g.month, count(t) from generate_series( (select date_trunc('year', min(t.time)) from t), (select date_trunc('year', max(t.time)) + interval '11 months' from t), interval '1 month' ) as g(month) left outer join t on t.id_object = 1 and t.status = 1 and date_trunc('month', t.time) = g.month where date_trunc('year', g.month) = '2014-01-01'::date group by g.month order by g.month