🧠 SQL PARTITION BY: Split Your Data into Groups

Xorthax noticed something interesting—looking at totals across all customers wasn’t always helpful. He needed to analyze each customer separately.

That’s where PARTITION BY comes in.

🚀 PARTITION BY + OVER()

You already know that OVER() lets a function look across rows.

PARTITION BY changes which rows it looks at.

FUNCTION(...) OVER (
  PARTITION BY column
)

Instead of using all rows, the function now:

• Splits the data into groups
• Runs the calculation separately for each group

📊 Example: Total per Customer

🧾 Sample Data

SELECT OrderID, CustomerID, Amount,
       SUM(Amount) OVER (PARTITION BY CustomerID) AS CustomerTotal
FROM Orders;

Result:

Each customer now has their own total, calculated separately.

🧠 What’s Happening Here?

PARTITION BY CustomerID splits the data into groups:

• Group A → rows for customer A
• Group B → rows for customer B

Then SUM() runs inside each group and attaches the result to every row in that group.

The calculation resets for each partition.

🧪 Practice What You Just Learned

SELECT customerid, orderid, totalamount, SUM(totalamount) OVER (PARTITION BY customerid) AS customer_total FROM orders ORDER BY customerid, orderid;

➡️ Next Step

Right now, each group is treated as a whole.

Next, you’ll learn how to control the order of rows within each group.

Want to go deeper?