Postgresql group month wise 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 count all rows with status=1 and id_object=1 monthwise for example. this is my query:

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 is:

2
1

2 for may and 1 for june but i need a output with all 12 months, also months with no data. for this example i need this ouput:

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

Thx for help.

roman · Accepted Answer · 2014-06-11 07:00:45Z

6

you can use generate_series() function like this:

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

sql fiddle demo

answered Jun 11, 2014 at 7:00

roman

118k30 gold badges205 silver badges209 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Clockwork-Muse Over a year ago

Except using something like EXTRACT(...) will cause the db to ignore indices (will be slow on large tables).

Clodoaldo Neto Over a year ago

@Clockwork-Muse Except if the indexes were built using extract()

roman Over a year ago

@Clockwork-Muse correct, but it was not the point of the answer :)

Clockwork-Muse · Accepted Answer · 2014-06-11 22:44:14Z

1

Rather than comparing with an extracted value, you'll want to use a range-table instead. Something that looks 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'll start with generate_series(), this time using it to generate the 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

The use of the range will allow any index including My_Table.time to be used (although not if an index was built over an EXTRACTed column.

EDIT:

Modified query to take advantage of the fact that generate_series(...) will also handle date/time series.

edited Jun 11, 2014 at 22:44

answered Jun 11, 2014 at 11:38

Clockwork-Muse

13.2k6 gold badges33 silver badges50 bronze badges

2 Comments

Clodoaldo Neto Over a year ago

That is not how interval's arithmetic is done in Postgresql. You just do '2014-01-01'::date + (month - 1) * interval '1 month'. No casting. No string building.

Clockwork-Muse Over a year ago

@ClodoaldoNeto - thanks for pointing that out. I'm (still) used to DB2, which doesn't require dealing with any of the operands with strings. Actually, your showing that generate_series(...) deals with date/time/timestamps is more useful (I probably need to read more of the reference for PostgreSQL).

Clodoaldo Neto · Accepted Answer · 2014-06-12 09:30:29Z

0

generate_series can generate timestamp 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

edited Jun 12, 2014 at 9:30

answered Jun 11, 2014 at 14:42

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

2 Comments

Clockwork-Muse Over a year ago

Except, given that it's likely the table doesn't have dates from the future, you won't have the full range of months. Depending on the given id_object you don't know what time might be missing. And unless you've generated an index including DATE_TRUNC(...) (less likely than one including the base column), this will still ignore indices.

Clodoaldo Neto Over a year ago

@Clockwork-Muse Ok I fixed the missing months part. About the ignore indices part that is an interesting point but I don't have the time now to discuss it.

Collectives™ on Stack Overflow

Postgresql group month wise with missing values

3 Answers 3

3 Comments

EDIT:

2 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

EDIT:

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related