Steve Jones does a bit of aggregation:
I looked at row_number() in a previous post. Now I want to build on that and do some counting of rows with COUNT() and the OVER clause. I’ll show how this differs a bit from a normal aggregate.
Read on to see one example of how aggregation window functions (available in SQL Server since 2012) are so powerful.