current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 2 down vote favorite

What I want to get is a statistic with each month from a generate_series and the sum of the counted id's in every month. This SQL works in PostgreSQL 9.1: 

  SELECT (to_char(serie,'yyyy-mm')) AS year, sum(amount)::int AS eintraege FROM (
    SELECT  
       COUNT(mytable.id) as amount,   
       generate_series::date as serie   
       FROM mytable  

    RIGHT JOIN generate_series(  

       (SELECT min(date_from) FROM mytable)::date,   
       (SELECT max(date_from) FROM mytable)::date,  
       interval '1 day') ON generate_series = date(date_from)  
       WHERE version = 1   
       GROUP BY generate_series       
       ) AS foo  
  GROUP BY Year   
  ORDER BY Year ASC;

And this is my output

"2006-12" | 4  
"2007-02" | 1  
"2007-03" | 1

But what I want to get is this output ("0" value in January): 

"2006-12" | 4  
"2007-01" | 0  
"2007-02" | 1  
"2007-03" | 1

So if there is a month with no id it should be listed nevertheless. Any ideas how to solve this?

Here is some sample data: 

drop table if exists mytable;
create table mytable(id bigint, version smallint, date_from timestamp without time zone);
insert into mytable(id, version, date_from) values

('4084036', '1', '2006-12-22 22:46:35'),
('4084938', '1', '2006-12-23 16:19:13'),
('4084938', '2', '2006-12-23 16:20:23'),
('4084939', '1', '2006-12-23 16:29:14'),
('4084954', '1', '2006-12-23 16:28:28'),
('4250653', '1', '2007-02-12 21:58:53'),
('4250657', '1', '2007-03-12 21:58:53')
;
share|improve this question
1  
As always, table definition of mytable should be in your question. And some example values to go with it would be swell. –  Erwin Brandstetter Nov 16 '12 at 10:17
add comment

1 Answer

up vote 6 down vote accepted

Untangled, simplified and fixed, it might look like this:

SELECT to_char(s.tag,'yyyy-mm') AS monat
      ,count(t.id) AS eintraege
FROM  (
   SELECT generate_series(min(date_from)::date
                         ,max(date_from)::date
                         ,interval '1 day'
          )::date AS tag
   FROM   mytable t
   ) s
LEFT   JOIN mytable t ON t.date_from::date = s.tag AND t.version = 1   
GROUP  BY 1
ORDER  BY 1;

Among all the noise, misleading identifiers and unconventional format the actual problem was hidden here:

WHERE version = 1

While you made correct use of RIGHT JOIN, you voided the effort by adding a WHERE clause that required a distinct value from mytable- converting the RIGHT JOIN to a JOIN effectively.

Pull the clause down into the JOIN condition to make this work.

I simplified a couple of other things.

share|improve this answer
    
Thank you very much for your answer and especially the explanation! Your answer solves the problem. –  C.B. Nov 16 '12 at 11:13
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.