Database.Total.Give examples with Sum(), Avg(), Max()

Let’s explore how to use window functions with SUM(), AVG(), and MAX() in SQL.

These functions compute aggregates over a “window” of rows, but unlike GROUP BY, they don’t collapse rows — they return a result per row.

---

🎯 Sample Table: sales

sale_id	employee_id	sale_amount	sale_date
1	101	100	2024-06-01
2	101	150	2024-06-02
3	102	200	2024-06-01
4	101	120	2024-06-03
5	102	180	2024-06-02

---

✅ 1. SUM() — Running Total per Employee

SELECT
  employee_id,
  sale_date,
  sale_amount,
  SUM(sale_amount) OVER (
    PARTITION BY employee_id
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM sales;

🔍 What it does:

• Partitions by employee_id
• Orders by sale_date
• For each row: sums all previous + current rows in the window

✅ 2. AVG() — Average Sale per Employee

SELECT
  employee_id,
  sale_date,
  sale_amount,
  AVG(sale_amount) OVER (
    PARTITION BY employee_id
  ) AS avg_sale_per_employee
FROM sales;

🔍 What it does:

• Computes the average sale_amount per employee
• Same result on all rows for the same employee

✅ 3. MAX() — Max Sale So Far (Running Max)

SELECT
  employee_id,
  sale_date,
  sale_amount,
  MAX(sale_amount) OVER (
    PARTITION BY employee_id
    ORDER BY sale_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS max_so_far
FROM sales;

🔍 What it does:

• Tracks the maximum sale up to the current date
• One running max per employee

🧠 Summary Table

Function	Typical Use	With Window Function Example
SUM()	Running totals	SUM(...) OVER (...)
AVG()	Average per group or globally	AVG(...) OVER (...)
MAX()	Cumulative or group max	MAX(...) OVER (...)