View Single Post
Old October 19th, 2006, 11:44 AM
lethe lethe is offline
Authorized User
Join Date: Oct 2006
Location: , , .
Posts: 10
Thanks: 0
Thanked 1 Time in 1 Post

quote:Originally posted by Jeff Moden

...and, in a properly indexed environment, will process millions of rows in scant seconds (ie. 4 million rows in about 5-7 seconds).

--Jeff Moden
Hi Jeff-

That was a really cool SQL example. Much more sophisticated than anything I was trying. I've learned some stuff by playing with it and trying to understand it (for example, I didn't know that you can SELECT ... FROM (SELECT FROM ...) AS tablevariable query).

Also, I've never used WITH ROLLUP before. I read some pages on the internet to try to understand what it does, but it's kind of moot; I couldn't get my server to accept any of my WITH ROLLUP examples (including the one you posted). Is there something special about the query syntax which must be satisfied to use WITH ROLLUP? My server just says "Incorrect syntax near WITHROLLUP" for example with the query you posted. Do I have to have a GROUPING statement in the query?

If I remove the WITH ROLLUP from your query, I get the table I want with one problem; some of my rows have Year = NULL and then all of the month counts are zero. These rows have some nonzero number in their Total count column. I don't understand where these nonzero numbers come from. Is it a by product of how GROUP BY works? It's not that big a deal, but I'm just trying to understand how all this works.

Anyway, thanks for your help. If I get my problems resolved, I will post back. And if you feel like giving further advice, please do. Once again, thanks for your post, Jeff, which was quite helpful.