AVG() / COUNT() / MIN() / MAX() OVER

Window aggregates (calculations that summarize a group) stamp a summary stat.

About 5–10 minutes · no sign-up needed

Example

Adding Category Context with Aggregate Window Functions

SELECT order_id, category, revenue,
       AVG(revenue) OVER (PARTITION BY category) AS avg_category_revenue,
       COUNT(*) OVER (PARTITION BY category) AS orders_in_category,
       MIN(revenue) OVER (PARTITION BY category) AS min_category_revenue,
       MAX(revenue) OVER (PARTITION BY category) AS max_category_revenue
FROM orders

Result:

order_id	category	revenue	avg_category_revenue	orders_in_category	min_category_revenue	max_category_revenue
O2001	Electronics	299.99	316.16	3	149.5	499.0
O2002	Electronics	149.5	316.16	3	149.5	499.0
O2005	Electronics	499.0	316.16	3	149.5	499.0
O2003	Clothing	59.95	74.48	2	59.95	89.0
O2004	Clothing	89.0	74.48	2	59.95	89.0

Step by step

Run the query without COUNT so you can see what the unfiltered values look like.

SELECT order_id, category, revenue,
       AVG(revenue) OVER (PARTITION BY category) AS avg_category_revenue,
       * OVER (PARTITION BY category) AS orders_in_category,
       MIN(revenue) OVER (PARTITION BY category) AS min_category_revenue,
       MAX(revenue) OVER (PARTITION BY category) AS max_category_revenue
FROM orders;

COUNT tells you how many rows match - perfect for sizing a table.

SELECT order_id, category, revenue,
       AVG(revenue) OVER (PARTITION BY category) AS avg_category_revenue,
       COUNT(*) OVER (PARTITION BY category) AS orders_in_category,
       MIN(revenue) OVER (PARTITION BY category) AS min_category_revenue,
       MAX(revenue) OVER (PARTITION BY category) AS max_category_revenue
FROM orders;

AS gives the COUNT result a friendly column name so the output reads cleanly.

SELECT order_id, category, revenue,
       AVG(revenue) OVER (PARTITION BY category) AS avg_category_revenue,
       COUNT(*) AS count_result OVER (PARTITION BY category) AS orders_in_category,
       MIN(revenue) OVER (PARTITION BY category) AS min_category_revenue,
       MAX(revenue) OVER (PARTITION BY category) AS max_category_revenue
FROM orders;

Practice

Try each one in your head first, then open the answer.

1. Using orders, write a query to find the average of the revenue column.

SELECT AVG(revenue) FROM orders

2. Using store_sales, write a query to find the average of the revenue column.

SELECT AVG(revenue) FROM store_sales

3. Using products, write a query to find the average of the price column.

SELECT AVG(price) FROM products

4. Using reviews, write a query to find the average of the rating column.

SELECT AVG(rating) FROM reviews

5. Using employees, write a query to find the average of the salary column.

SELECT AVG(salary) FROM employees

Learn it by doing. This lesson is a short hands-on level on Bampaa: see it work, then build and write it yourself, with instant feedback.

Try this lesson free →

About 5–10 minutes · no sign-up needed