Window functions in SQL Server

we have 3 type of Window functions, many of the candidate is not aware about the window function during interview session

• Aggregate Window Functions
• SUM(), MAX(), MIN(), AVG(). COUNT()
• Ranking Window Functions
• RANK(), DENSE_RANK(), ROW_NUMBER(), NTILE()
• Value Window Functions
• LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE()

Working

    We all know the aggregate functions, SUM(), MAX(), MIN(), AVG(). COUNT(), if these are executed on whole table then we need to GROUP BY clause in a query. and It will return single result for a query.

These can also used with Over (partition by ) clause in a query. In this case no need to mention group by clause and aggregate function will be return result for each row.

Example of Query, aggregate function with group by clause

SELECT c1, SUM(c2) alias FROM table_name GROUP BY c1;

Example of query, aggregate function without group clause

SELECT c1, c2, c3, c4, c5 ,SUM(c6) OVER(PARTITION BY c2) as alias FROM table_name;

Note* - You can not include the ID (PRIMARY_KEY column of table) while using aggregate function with GROUP by clause.

But we can use ID column in query, when aggregate function using as window function.

This post is for intend to know only type of window function.