SQL OVER PARTITION BY clause has both similarities and differences with SQL GROUP BY clause:

Similarities:

  • Both are used to summarise rows that have the same values. For example, to find the total donation amount from donors in each city
  • Often used with aggregation functions e.g. MIN(), MAX(), AVG(), SUM(), and COUNT()

Differences:

GROUP BYOVER PARTITION BY
Cannot add other columns from the table to the result without either adding them to the GROUP BY clause or to an aggregate functionCan add other columns from the table to the result
The number of records resulting from the query is the same as the number of values partitioned byThe number of records resulting from the query is the same as the number of records of the table

These will be quickly demonstrated in this article.

1. Prepare Sample Data

Use the below SQL script to create the sample table:

CREATE TABLE Donations
(
    DonationID INT,
    DonationDate DATE,
    DonationMethod VARCHAR(20),
    DonorName VARCHAR(100),
    DonorCity VARCHAR(100), 
    Amount MONEY
)

Use the below SQL script to create sample data (18 records) for the Donations table:

INSERT INTO Donations VALUES(1111, '01-01-2021', 'Cash', 'Adam Smith', 'New York', 100.00);
INSERT INTO Donations VALUES(1112, '01-01-2021', 'Credit Card', 'Brian Wilson', 'New York', 200.00);
INSERT INTO Donations VALUES(1113, '02-02-2021', 'Cash', 'Martin Ferguson', 'New York', 300);
INSERT INTO Donations VALUES(1114, '03-03-2021', 'Cash', 'Cris Ronoaldo', 'Chicago', 400.00);
INSERT INTO Donations VALUES(1115, '04-04-2021', 'Credit Card', 'Alex Feldon', 'Chicago', 500.00);
INSERT INTO Donations VALUES(1116, '05-05-2021', 'Cash', 'Christie Fraser', 'Phoenix', 600.00);
INSERT INTO Donations VALUES(1117, '06-06-2021', 'Direct Debit', 'Rob Davis', 'Phoenix', 700.00);
INSERT INTO Donations VALUES(1118, '07-07-2021', 'Direct Debit', 'Patricia Brown', 'Houston', 800.00);
INSERT INTO Donations VALUES(1119, '08-08-2021', 'Direct Debit', 'Justin Jones', 'Houston', 900.00);
INSERT INTO Donations VALUES(1120, '09-09-2021', 'Cash', 'Will Smith', 'San Jose', 100.00);
INSERT INTO Donations VALUES(1121, '10-10-2021', 'Cheque', 'Angela Jolie', 'San Jose', 200.00);
INSERT INTO Donations VALUES(1122, '11-11-2021', 'Cheque', 'Jenny Aniston', 'San Jose', 300.00);
INSERT INTO Donations VALUES(1123, '12-12-2021', 'Direct Debit', 'Grace Smith', 'New York', 400.00);
INSERT INTO Donations VALUES(1124, '01-01-2022', 'Cheque', 'Adam Williams', 'Chicago', 500.00);
INSERT INTO Donations VALUES(1125, '01-01-2022', 'Credit Card', 'Katie Smith', 'Los Angeles', 100.00);
INSERT INTO Donations VALUES(1126, '01-01-2022', 'Cheque', 'Donna Taylor', 'New York', 200.00);
INSERT INTO Donations VALUES(1127, '01-01-2022', 'Credit Card', 'Emma Smith', 'Phoenix', 300.00);
INSERT INTO Donations VALUES(1128, '01-01-2022', 'Cash', 'Mia Smith', 'Phoenix', 400.00);

After execution of the above INSERT statements, the Donations table is populated with 18 records as below:

2. SQL GROUP BY Clause

Use the below SQL script to find minimum, maximum, and total donation amount per city:

SELECT DonorCity, 
       MIN(Amount) AS MinAmount,
       MAX(Amount) AS MaxAmount,
       SUM(Amount) AS TotalAmount
FROM Donations
GROUP BY DonorCity;

The result is below:

We can that there is only five rows — accordingly to five cities — resulting from the query.

With GROUP BY, if we add other columns e.g. DonorName to the SELECT clause without adding them to the GROUP BY clause, the query fails when it is executed. Below is the error message:

To overcome this limitation we can use the OVER PARTITION BY clause.

3. SQL OVER PARTITION BY Clause

Use the below SQL script to find minimum, maximum, and total donation amount per city with also having the donor names displayed in the result:

SELECT DonorCity,
       DonorName,
       MIN(Amount) OVER (PARTITION BY DonorCity) AS MinAmount,
       MAX(Amount) OVER (PARTITION BY DonorCity) AS MaxAmount, 
       SUM(Amount) OVER (PARTITION BY DonorCity) AS TotalAmount
FROM Donations

The result is below:

We see that there are 18 rows — the same as the number of rows in the Donations table — resulting from the query.