SUM() OVER (PARTITION BY) - partition total

SUM() OVER (PARTITION BY col) calculates the total for each group and attaches it to every row in that group - no rows are collapsed.

About 5–10 minutes · no sign-up needed

Example

Partition Total with SUM() OVER (PARTITION BY)

SELECT order_id, customer_id, category, revenue,
  SUM(revenue) OVER (PARTITION BY category) AS category_total
FROM orders

Result:

order_id	customer_id	category	revenue	category_total
O2001	C101	Electronics	299.99	598.99
O2002	C102	Electronics	149.5	598.99
O2004	C103	Electronics	149.5	598.99
O2005	C102	Clothing	210.0	299.0
O2003	C101	Clothing	89.0	299.0

Start with the raw columns - order_id, category, and revenue.
```
SELECT order_id, customer_id, category, revenue
FROM orders;
```
PARTITION BY category groups the window by category. No ORDER BY means the SUM covers the whole partition, not just rows so far.
```
SELECT order_id, customer_id, category, revenue,
  SUM(revenue) OVER (PARTITION BY category)
FROM orders;
```

AS category_total names the new column clearly in the output.

SELECT order_id, customer_id, category, revenue,
  SUM(revenue) OVER (PARTITION BY category) AS category_total
FROM orders;

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

1. Using orders, add a column showing the total revenue for each category beside every order row.

SELECT *, SUM(revenue) OVER (PARTITION BY category) FROM orders

2. Using store_sales, add a column showing the total revenue for each channel beside every sale row.

SELECT *, SUM(revenue) OVER (PARTITION BY channel) FROM store_sales

3. Using products, add a column showing the total price for each category beside every product row.

SELECT *, SUM(price) OVER (PARTITION BY category) FROM products

4. Using employees, add a column showing the total salary for each department beside every employee row.

SELECT *, SUM(salary) OVER (PARTITION BY department) FROM employees

5. Using reviews, add a column showing the total rating for each product_id beside every review row.

SELECT *, SUM(rating) OVER (PARTITION BY product_id) FROM reviews

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.

About 5–10 minutes · no sign-up needed