Homework Day 2 Postgres
29 Mar 2012, 12:00
Max Holzapfel (1 post)
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 | | |
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.
20 May 2012, 11:02
Sean Carmody (2 posts)
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)
|You must be logged in to comment|