Do you think it is not possible to aggregate values without using clause "GROUP BY"?
I'll show you how to do it in SQL 2012/2014
At first, will create a table for ten years of sales and fill it with values:
Now we have following aggregation request:
Report all sales aggregated by a year with a percentage of each year within whole data set;
Not a big deal. That how it would look like in classic scenario:
Here is an approximate result:
And now will do the Magic
The result is exactly the same, but code does not have any single "GROUP BY" and not so obvious.
Is it good?
Turn On statistics in your session by running two following commands:
SET STATISTICS IO ON
SET STATISTICS TIME ON
And then run both scripts together.
If after execution you look at Messages tab you can see following picture:
"GROUP BY" uses 5 times less CPU power and almost 1000 times less IO system!!!
As a conclusion: You CAN use "OVER"clause to do the aggregation in three following cases:
1. When data set is extremely small and fits in just one 8 Kb page;
2. When you want to hide your logic from any future developer or even yourself to make debugging and troubleshooting a nightmare;
3. When you really want to kill your SQL Server and its underlying disk system;