10 Nov 2012, 14:25
Me_pragsmall

Chad Ostrowski (8 posts)

I inserted a bunch of extra events, so my events-by-month-and-year should have three rows for three years.

This is my crosstab code (which matches page 34 of the book exactly, as far as I can see):

SELECT * FROM crosstab(
  'SELECT extract(year from starts) as year,
          extract(month from starts) as month,
          count(*)
   FROM events
   GROUP BY year, month',
   'SELECT * FROM month_count'
) AS (
  year int,
  jan int, feb int, mar int, apr int, may int, jun int,
  jul int, aug int, sep int, oct int, nov int, dec int
) ORDER BY year;

But this is the result:

 year | jan | feb | mar | apr | may | jun | jul | aug | sep | oct | nov | dec 
------+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----+-----
 2012 |     |   2 |     |     |     |     |     |     |     |     |     |    
 2012 |     |     |     |     |   1 |     |     |     |     |     |     |    
 2013 |     |   2 |     |     |   1 |     |     |     |     |     |     |    
 2013 |     |     |     |     |     |   1 |     |   1 |     |     |     |    
 2013 |   2 |     |   1 |   1 |     |     |     |     |     |     |     |   1
 2014 |     |     |     |     |     |     |     |     |   1 |     |     |    
(6 rows)

I can’t see any differences between the events in different months of the same year that would make them be grouped differently. What’s going on?

05 Dec 2012, 15:35
Avatar_code_90_pragsmall

Aaron McBride (4 posts)

I had a similar problem and found the answer to this post to be helpful in this case: http://forums.pragprog.com/forums/202/topics/10557

Slap an “ORDER BY year, month” on the first query passed to crosstab().

  You must be logged in to comment