Learn T-SQL With Erik: A Neat GROUP BY Trick
Going Further
If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 75% discount to my blog readers if you click from here. I’m also available for consulting if you just don’t have time for that, and need to solve database performance problems quickly. You can also get a quick, low cost health check with no phone time required.
Ah yes, one of my favourite things to do, especially in ETL/ELT. Makes everything so much simpler when you only have to update your expressions in one place.
I’ve had to use Snowflake recently and while it rubs me every sort of wrong way on the relational side of things, they have a GROUP BY ALL syntactic sugar that does what you’d expect in addition to being able to reference expression aliases “earlier” than you’re used to with SQL Server. It’ll be interesting to see if the query parser and processor for SQL Server ever acquire such features.
Ah, that is cool. I really like what a lot of these other products have been doing with extending SQL in meaningful ways. DuckDB is a particularly good example.
Erik,
Awesome video! I’ve been using this trick for a while now and often urge others to leverage it to clean up their queries. I know you called it a neat GROUP BY trick but I would argue it’s a neat GROUP BY, HAVING, subsequent JOIN, subsequent APPLY, SELECT, EXISTS, ORDER BY trick since it really makes the alias available to just about all other areas of the query! You’re cool with blog titles being 200 characters long, right…?
Well, what it loses in snappiness it probably gains in SEO, hahaha