Homework Day 2 Postgres

Hi!

First let me say that I love your book! It is a nice overview. It would be nice if you could give some follow up reading for each DB.

Regarding the home work:
I started of with this
SELECT * FROM crosstab(' SELECT extract(week from starts) as week, extract( dow from starts) as day, count(*) from events GROUP BY week, day', 'SELECT * FROM generate_series(0, 6)' ) AS ( week int, SUN int, MON int, TUE int, WED int, THU int, FRI int, SAT int) ORDER BY week;

But somehow one week 7 is giving me trouble:
week | sun | mon | tue | wed | thu | fri | sat
—-----—-—-—-—-—+-- 7 | | 1 | | 1 | | | 7 | | | 1 | | | | 13 | 1 | | | | | | 14 | | | 1 | | | | 15 | | 1 | | | | | 18 | | | | | 1 | | 25 | | | | | | | 1 52 | | | 1 | | | |

After reading the assignment again I saw that you want ONE month. Thus I used WHERE and extracted all events in feb.

week | sun | mon | tue | wed | thu | fri | sat 
---------------------+--
   7 |     |   1 |   1 |   1 |     |     |

Oddly enough now week 7 shows up perfectly. Why? I haven’t change a thing but the WHERE bit.

Although I couldn’t reproduce your problem (could be to do with how I created my events), it is always a good idea to order the results in the SELECT statement passed to crosstab:

... COUNT(*) FROM events GROUP BY week, day ORDER BY week, day ...

does that help?

Also, when using generate_series, the * is redundant:

SELECT generate_series(0, 6)

will do.