Window Functions in SQL Server

Cuantos no hemos intentado hacer esto :
SELECT ID,
Value,
SUM(Value) AS "Sum"
AVG(Value) AS "Avg"
COUNT(Value) AS "Quantity" FROM TestAggregation
GROUP BY ID;

error!!!!!!!!!!!

Msg 8120, Level 16, State 1, Line 2

Column 'TestAggregation.Value' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.


se podria resolver con consultas anidadas pero esto es inficiente y el rendimiento pues ni que decir..


con window functions lo resolvemos de un manera elegante ...

SELECT ID,
Value,
SUM(Value) OVER(PARTITION BY ID) AS "Sum"
AVG(Value) OVER(PARTITION BY ID) AS "Avg"
COUNT(Value) OVER(PARTITION BY ID) AS "Quantity"
FROM TestAggregation



para mas informacion, checalo en el siguiente enlace...
http://www.simple-talk.com/sql/learn-sql-server/window-functions-in-sql-server/