🧠 Aggregate Window Functions: SUM, COUNT, AVG, MIN, MAX

So far, Xorthax has been using SUM() with OVER(). But totals aren’t the only useful metric.

You can apply many aggregate functions using the same pattern.

🚀 Aggregate Functions + OVER()

SUM(...)   OVER (...)
COUNT(...) OVER (...)
AVG(...)   OVER (...)
MIN(...)   OVER (...)
MAX(...)   OVER (...)

Each function:

• Looks across multiple rows
• Returns a value for each row

📊 Example: Multiple Metrics per Customer

SELECT OrderID, CustomerID, Amount,
       SUM(Amount)   OVER (PARTITION BY CustomerID) AS Total,
       COUNT(*)      OVER (PARTITION BY CustomerID) AS OrderCount,
       AVG(Amount)   OVER (PARTITION BY CustomerID) AS AvgAmount,
       MIN(Amount)   OVER (PARTITION BY CustomerID) AS MinAmount,
       MAX(Amount)   OVER (PARTITION BY CustomerID) AS MaxAmount
FROM Orders;

Result:

Each row now includes multiple metrics for its customer.

🧠 What’s Happening Here?

PARTITION BY CustomerID creates a group for each customer.

Each function runs across that group:

• SUM() → total amount over the partition
• COUNT() → number of rows over the partition
• AVG() → average value over the partition
• MIN() → smallest value over the partition
• MAX() → largest value over the partition

The results are attached to every row in the group.

🧪 Practice What You Just Learned

SELECT customerid, orderid, totalamount, AVG(totalamount) OVER (PARTITION BY customerid) AS avg_order_amount FROM orders ORDER BY customerid, orderid;

➡️ Next Step

So far, each function looks at all rows in the group.

Next, you’ll learn how to control which rows are included.

Want to go deeper?