Window Functions in SQL
SQL window functions allow performing calculations across a set
of rows that are related to the current row, without collapsing the
result into a single value. They are commonly used for tasks like
aggregates, rankings and running totals.
The OVER clause defines the “window” of rows for the calculation.
It can:
PARTITION BY: divide the data into groups.
ORDER BY: specify the order of rows within each group.
Syntax
SELECT column_name1,
window_function(column_name2)
OVER ([PARTITION BY column_name3] [ORDER BY
column_name4]) AS new_column
FROM table_name;
Types of Window Functions in SQL
1. aggregate window functions
2. ranking window functions.
Aggregate Window Function
Aggregate window functions calculate aggregates over a window
of rows while retaining individual rows. Common aggregate
functions include:
SUM(): Sums values within a window.
AVG(): Calculates the average value within a window.
COUNT(): Counts the rows within a window.
MAX(): Returns the maximum value in the window.
MIN(): Returns the minimum value in the window.
Practical-
CREATE DATABASE CompanyDB;
USE CompanyDB;
Create Table
CREATE TABLE Employees (
Name VARCHAR(50),
Age INT,
Department VARCHAR(50),
Salary INT
);
Insert data into Employees table
INSERT INTO Employees (Name, Age, Department, Salary) VALUES
('Ramesh', 20, 'Finance', 50000),
('Suresh', 22, 'Finance', 50000),
('Ram', 28, 'Finance', 20000),
('Deep', 25, 'Sales', 30000),
('Pradeep', 22, 'Sales', 20000);
Question -1 Using AVG() to Calculate Average Salary within
each department?
Solution –
SELECT Name, Age, Department, Salary,
AVG(Salary) OVER( PARTITION BY Department) AS
Avg_Salary
FROM employee
Ag Departme Salar Avg_Sala
Name e nt y ry
Rames 50,00
20 Finance 40,000
h 0
50,00
Suresh 22 Finance 40,000
0
20,00
Ram 28 Finance 40,000
0
30,00
Deep 25 Sales 25,000
0
Pradee 20,00
22 Sales 25,000
p 0
Question -2 Show each employee with department-wise total
salary
Query:
SELECT Name, Department, Salary,
SUM(Salary) OVER (PARTITION BY Department) AS Total_Salary
FROM Employees;
Solution -
Ramesh Finance 50000 120000
Suresh Finance 50000 120000
Ram Finance 20000 120000
Deep Sales 30000 50000
Pradeep Sales 20000 50000
Practice question
Question – 1 Show each employee with maximum salary in their department.
Question-2 Show each employee with minimum salary in their department.
Question – 3 Count number of employees in each department using window function.
. Ranking Window Functions
These functions provide rankings of rows within a partition based
on specific criteria. Common ranking functions include:
RANK(): Assigns ranks to rows, skipping ranks for
duplicates.
DENSE_RANK(): Assigns ranks to rows without skipping
rank numbers for duplicates.
ROW_NUMBER(): Assigns a unique number to each row in
the result set
RANK Function
It assigns ranks to rows within a partition, with the same rank
given to rows with identical values. If two rows share the same
rank, the next rank is skipped
Example: Using RANK() to Rank Employees by Salary
SELECT Name, Department, Salary,
RANK() OVER(PARTITION BY Department ORDER BY Salary
DESC) AS emp_rank
FROM employee;
Output
Departme Salar emp_ra
Name nt y nk
Rames 50,00
Finance 1
h 0
50,00
Suresh Finance 1
0
20,00
Ram Finance 3
0
30,00
Deep Sales 1
0
Pradee 20,00
Sales 2
p 0
Explanation:
RANK() function assigns a ranking within each
department based on salary (highest salary = rank 1).
DENSE RANK Function
Example: Using DENSE_RANK() to Rank Employees by Salary
SELECT Name, Department, Salary,
DENSE_RANK() OVER(PARTITION BY Department ORDER BY
Salary DESC) AS emp_dense_rank
FROM employee;
Output
Departme Salar emp_dense_r
Name nt y ank
Rames 50,00
Finance 1
h 0
50,00
Suresh Finance 1
0
20,00
Ram Finance 2
0
30,00
Deep Sales 1
0
Pradee 20,00
Sales 2
p 0
ROW NUMBER Function
ROW_NUMBER() gives each row a unique number. It numbers
rows from one to the total rows. The rows are put into groups
based on their values.
Example: Using ROW_NUMBER() for Unique Row Numbers
SELECT Name, Department, Salary,
ROW_NUMBER() OVER(PARTITION BY Department ORDER BY
Salary DESC) AS emp_row_no
FROM employee;
Output
Departme Salar emp_row_
Name nt y no
Rames 50,00
Finance 1
h 0
50,00
Suresh Finance 2
0
20,00
Ram Finance 3
0
30,00
Deep Sales 1
0
Pradee 20,00
Sales 2
p 0