GROUP BY vs Window Functions in SQL

GROUP BY vs Window Functions in SQL (Beginner-Friendly Explanation)

One of the most important SQL concepts for beginners is understanding the difference between
👉 GROUP BY and Window Functions (OVER()).

They may look similar, but they solve very different problems.

Let’s understand this slowly, with simple tables.

🧠 The Core Difference (In One Line)

• GROUP BY → Reduces rows
• Window Function → Keeps all rows

📊 Sample Data

❌ Using GROUP BY

Question:

👉 What is the total amount?

SELECT SUM(amount)
FROM sales;

Result:

450

✔ Correct total
❌ But all rows are collapsed into one row

You lose row-level details like Day 1, Day 2, Day 3.

✅ Using a Window Function

Same question, different need:

👉 Show the total along with each row

SELECT
  day,
  amount,
  SUM(amount) OVER () AS total_amount
FROM sales;

Result:

✔ Total is shown
✔ All rows are preserved

🔍 Why This Matters

GROUP BY is good when:

• You want summary data
• You don’t care about individual rows

Example:

• Total sales
• Average salary per department

Window Functions are good when:

• You need both detail and summary
• You want calculations per row

Example:

• Running totals
• Rank within department
• Opening vs closing balance

📌 Another Common Example

Average salary per department

GROUP BY

SELECT department, AVG(salary)
FROM employees
GROUP BY department;

✔ One row per department

Window Function

SELECT
  employee,
  department,
  salary,
  AVG(salary) OVER (PARTITION BY department) AS dept_avg
FROM employees;

✔ Every employee row stays
✔ Department average is visible

🧠 Easy Way to Remember

If rows disappear → GROUP BY
If rows stay → Window Function

🎯 Interview Tip

If a question says:

• “Show each row along with…” → Think window function
• “Give me one result per group” → Think GROUP BY

One-Line Summary

GROUP BY summarizes data.
Window functions analyze data without losing rows.