Learn T-SQL With Erik: A Neat GROUP BY Trick

Learn T-SQL With Erik: A Neat GROUP BY Trick

Video Summary

In this video, I share a quick and handy trick using GroupBy in SQL Server, perfect for those Friday afternoons when you just want to get your work done efficiently before heading off to enjoy the weekend. This isn’t a deep dive into the intricacies of GroupBy; instead, it’s a practical solution to an annoyance many of us face—how messy queries can become when grouping by expressions. I demonstrate how using `CROSS APPLY` with the `VALUES` construct can clean up your code and make it more readable without resorting to complex Common Table Expressions (CTEs) or other workarounds that can be a pain to remember. So, whether you’re preparing for the Precons for Past Data Summit or just looking for a way to streamline your SQL queries, this video has got you covered. Enjoy your weekend and happy coding!

Full Transcript

Erik Darling here with Darling Data. In today’s video, we’re going to do a quick little trick with GroupBy. The reason it’s a quick video is because this is getting published on a Friday, and golly and gosh, I don’t want to keep you anywhere waiting on a Friday. You should be gallivanting off to have a great weekend. So this is just a cool little trick. This is not going to be deep, fundamental, internal stuff about GroupBy and grouping tables and all the other stuff that’s in this session, whatever you want to call it. Of course, this is part of the companion material to Kendra Little and I’s Precons for Past Data Summit. If you are coming to the Precons, you will get free access to this material, since it’s companion material to the Precons. If you are not coming, you can get it for the presale price of $250 US dollars. That will be going up to $500 when the course is fully published. So, get in while the getting’s good, as they say. Eat while the chicken’s hot, or something. Smokey beans. Anyway, what are they going to say? Oh yeah, GroupBy. Fun stuff. So, one of my annoyances with GroupBy, at least in SQL Server, is how messy queries can get once you start grouping by some expression. Once you start writing things out, you’re like, I’ve got to write this over and over again. Gosh almighty. Even if you’re really good at copying and pasting, it gets very irritating.

And some code completion tools, I’m not going to name any names, have some issues in this area. So, this is what a typical, very, you know, I had to type too much query to do this GroupBy thing looks like, where we’re getting the date part for creation date. And then when the GroupBy, we got to do this. Now Oracle actually recently brought out some improvement to their SQL dialect. Where you can actually reference column, like, aliased columns in the GroupBy, which I think is fantastic.

T-SQL should have such a thing. SQL Server 2025. Where are you at, buddy? T-SQL should have such a thing. Now, of course, you can, because if you’ve been paying attention to my videos and the logical query processing stuff, you could actually reference them in the OrderBy. That would be okay. So, like, if we look at this query down here, like, we have some squiggles in the GroupBy. Like, the GroupBy is not going to work, but the OrderBy is totally kosher. Like, this part’s fine.

But, you know, if we try to run this, the SQL Server is going to be like, I don’t know. Like, who are these columns? I’ve never heard of them. Where did they come from? From whence did they came? Which is annoying to me. But, and this is probably one of the most frequent things that you will hear said about T-SQL. Don’t worry. There’s a workaround. Because it’s never just a straightforward way of like, hey, we can just do this. It’s always, there’s a workaround. Like, another one. Something else to memorize. And that is, of course, using the cross-apply with values construct clause thing, whatever you want to call it.

So, we’re going to use cross-apply in here. And in cross-apply, we are going to say values. And within those values, we are going to get the two expressions that we care about. Creation date, year and quarter, right? There’s year, there’s quarter. Sorry, it’s very pink on here today. And then we are going to alias those columns coming out of the cross-apply is creation year and creation quarter. And, of course, now we can run this query with absolutely no problems. And I, like, you know, assuming that you can cope with a little cross-apply diversion in your query like this, I do find that this makes the code much sort of cleaner and easier to deal with.

And you don’t have to write any goofy CTE and get into arguments with LinkedIn on people about how goofy CTE are. They don’t actually make queries more readable. Because who has time for that? Not me. Anyway, that’s my quick video today. Like I said, this is going on. Well, this is Friday now for you.

So, please do have a great weekend. Please do drive safely. Even if you’re not drinking, drive safely. Not drinking is no excuse to drive worse. I hope you enjoyed yourselves. I hope you learned something.

And remember, this is the pre-sale thing for the course down here. So, if you’re enjoying this at all, imagine if you had, like, a couple days worth of T-SQL content like this to watch and learn from. It might be pretty good, right?

It’s a whole lot better than reading some stiff book or some terrible documentation or just not knowing what you’re doing and hoping that there’s a Stack Overflow answer for you. Or hoping that the LLM was right because, hoo-wee, you got a lot of hoping to do there. Anyway, thank you for watching. Goodbye.

Going Further

If this is the kind of SQL Server stuff you love learning about, you’ll love my training. I’m offering a 25% 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.