Querying Databases Using Transact-SQL/

...

/

The PARTITION BY Clause

The PARTITION BY Clause

Learn how to group columns within T-SQL window functions.

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 ...