29 Mar 2012, 12:00
Generic-user-small

Max Holzapfel (1 post)

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.

20 May 2012, 11:02
Generic-user-small

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)

will do.

  You must be logged in to comment