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

SELECT employee_name, department, salary, AVG(salary) OVER (PARTITION BY department) FROM employees;

GROUP BY collapses rows, cannot show individual employee names. Use window function to preserve detail.

Use window functions when you need detail rows with aggregate context

High

Cannot select employee_name without GROUP BY or aggregate

SELECT department, COUNT(*) OVER (PARTITION BY department) FROM employees GROUP BY department;

SELECT department, COUNT(*) FROM employees GROUP BY department;

Use either GROUP BY (for summary) or OVER (for detail), not both in same SELECT level.

Choose GROUP BY for summaries, OVER for detail with context

High

Cannot mix GROUP BY and OVER in same SELECT