Window Functions in SQL Server: Simple Examples

08a - WINDOW FUNCTIONS  (VERY SIMPLE version)
---------------------------------------------------------------------
No setup needed. This file creates its own small table.

A window function adds a calculation to EVERY row,
without combining the rows like GROUP BY does.

SUM(column) OVER ()                         -> total of all rows
SUM(column) OVER (PARTITION BY Department)  -> total per department

Run the sample database script first. Every query below was run on that data, and the Output under each query is the real result.

IF DB_ID('SqlLearningDb') IS NULL
    CREATE DATABASE SqlLearningDb;
GO

USE SqlLearningDb;
GO

Small table: 7 staff members in 3 departments

DROP TABLE IF EXISTS dbo.Staff;

CREATE TABLE dbo.Staff
(
    StaffName  VARCHAR(20),
    Department VARCHAR(10),
    Salary     INT
);

INSERT INTO dbo.Staff (StaffName, Department, Salary) VALUES
('Ravi',   'IT',    50000),
('Sita',   'IT',    60000),
('John',   'IT',    40000),
('Meena',  'HR',    30000),
('Arjun',  'HR',    35000),
('Kiran',  'Sales', 45000),
('Pooja',  'Sales', 55000);
GO

SELECT * FROM dbo.Staff;

(7 rows affected)

GROUP BY: one row per department (names are gone)

SELECT Department, SUM(Salary) AS DeptTotal
FROM dbo.Staff
GROUP BY Department;

Window function: every row stays, total is added next to it

SELECT StaffName, Department, Salary,
       SUM(Salary) OVER (PARTITION BY Department) AS DeptTotal
FROM dbo.Staff;

OVER () with nothing inside = the whole table

SELECT StaffName, Salary,
       SUM(Salary) OVER () AS CompanyTotal
FROM dbo.Staff;

COUNT, AVG, MIN, MAX per department

SELECT StaffName, Department, Salary,
       COUNT(*)    OVER (PARTITION BY Department) AS PeopleInDept,
       AVG(Salary) OVER (PARTITION BY Department) AS DeptAverage,
       MIN(Salary) OVER (PARTITION BY Department) AS DeptLowest,
       MAX(Salary) OVER (PARTITION BY Department) AS DeptHighest
FROM dbo.Staff;

Compare each person with their department average

SELECT StaffName, Department, Salary,
       AVG(Salary) OVER (PARTITION BY Department)          AS DeptAverage,
       Salary - AVG(Salary) OVER (PARTITION BY Department) AS AboveOrBelow
FROM dbo.Staff;

Each person's share of the company salary (%)

SELECT StaffName, Salary,
       Salary * 100.0 / SUM(Salary) OVER () AS PercentOfTotal
FROM dbo.Staff;

Practice Questions

1. Show each person with the highest salary in the whole company.
2. Show each person with the number of people in the company.
3. Show each person's share (%) of their DEPARTMENT total.