Search⌘ K
Home
Courses
Querying Databases Using Transact-SQL

The PARTITION BY Clause

Understand how to use the PARTITION BY clause in T-SQL to perform grouping inside window functions. Learn to calculate aggregates for specific partitions while retaining full table output and improving query results with ordering.

We'll cover the following...

Aggregate functions are often used with GROUP BY so that calculations are done separately for each group:

CREATE DATABASE Competition;
USE Competition;

CREATE TABLE ActionPoints
(
    ActionId INT PRIMARY KEY IDENTITY(1, 1),
    Player NVARCHAR(100) NOT NULL,
    Points INT NOT NULL,
);

INSERT INTO ActionPoints (Player, Points)
VALUES
    ('Jeremy', 150),
    ('Alisa', 125),
    ('Lili', 160),
    ('Alisa', 95),
    ('Jeremy', 170),
    ('Lili', 115);

SELECT Player, AVG(Points) AS AveragePoints 
FROM ActionPoints
GROUP BY Player;
Finding the average number of points earned by each player for one action

Whenever we want to call an aggregate function in a window and perform a GROUP BY operation, simply adding a GROUP BY clause will result in an error:

CREATE DATABASE Competition;
USE Competition;

CREATE TABLE ActionPoints
(
    ActionId INT PRIMARY KEY IDENTITY(1, 1),
    Player NVARCHAR(100) NOT NULL,
    Points INT NOT NULL,
);

INSERT INTO ActionPoints (Player, Points)
VALUES
    ('Jeremy', 150),
    ('Alisa', 125),
    ('Lili', 160),
    ('Alisa', 95),
    ('Jeremy', 170),
    ('Lili', 115);

SELECT ActionId, Player, Points, AVG(Points) OVER() AS AveragePoints
FROM ActionPoints
GROUP BY Player;
Error as a result of adding a GROUP BY statement

From the output, we can see that our ...