Home / SQL / Reference / Window functions

SQL OVER and PARTITION BY

Turn an aggregate into a window function that keeps every row.

Syntax

aggregate(…) OVER (PARTITION BY … ORDER BY …)

PARTITION BY sets the groups; ORDER BY inside OVER makes running calculations.

Example

Query (on the shop database used in the SQL course)

SELECT name, category, price,
       ROUND(AVG(price) OVER (PARTITION BY category), 2) AS category_avg
FROM products
LIMIT 6;

Result

namecategorypricecategory_avg
Python Crash Course (book)Books45.053.0
Deep Learning (book)Books89.053.0
SQL Pocket GuideBooks25.053.0
Wireless mouseElectronics29.0155.0
Mechanical keyboardElectronics119.0155.0
USB-C hubElectronics49.0155.0

6 rows

Related

Learn it step by step in the free lesson CTEs & Window Functions, with exercises that check your queries.