SQL Server Aggregate Functions with OVER Clause
Did you know that you can use the SQL Server aggregate functions SUM, COUNT, MAX, MIN and AVG with an OVER Clause now?
Using an OVER clause you can produce individual record values along with aggregate values to different levels, without using a GROUP BY clause. You can also produce running totals, rolling averages, etc.
The examples in this article are based on the AdventureWorks2014 database.
Aggregate To Different Levels with OVER (PARTITION BY ….)