Last time, we were able to go over a way to group data that is probably not familiar to many. This time, I want to go over an extra piece to that puzzle that should add even more functionality to your repertoire when it comes to data functionality.
First, we have the basic query that we learned about last week:
Let’s say our business partner asks us to determine which fields are aggregated together. Since we only have 2 fields and a grand total of 15 rows, we could determine this by eye. But, like all good developers, we want to do this programmatically.
Here’s where our friend – the GROUPING() function – comes into play.
You’ll notice that the GROUPING function is simply called within the SELECT clause. The results though do seem a bit reversed as to what it’s showing – at least until you read the official Microsoft documentation here. The important section is shown below:
GROUPING returns 1 for aggregated or 0 for not aggregated in the result set.
In other words, if all of the data is aggregated under another column, then it will show 1. If it displays 0, then SQL Server is using the column as a data point.
Now, you might be thinking that you can just use the NULL fields to determine if the data is used to aggregate upon or not. What if your data has NULLs in it normally? Then, that idea goes out the window rather quickly.
With this new GROUPING function, I can do something I wasn’t able to do before – at least not easily. I can now order all of the data by how it was aggregated!
Definitely a tool to work with when it comes to ordering.
And with that my friends, we have come to the end of yet another blog post. Next time, we’ll attempt to bring you more cool functionality. Until then my friends!
1 comment