What is the fundamental difference between aggregate functions with GROUP BY and window functions with OVER?

GROUP BY collapses multiple rows into summary rows (one row per group). OVER preserves all detail rows while adding aggregate context. Use GROUP BY for summaries, OVER for detail with context.

When should you use GROUP BY vs window functions?

Use GROUP BY for summary reports, totals, and KPIs where you want collapsed data. Use window functions when you need to preserve detail rows while adding aggregate context, like showing each employee with department average.

Can you combine GROUP BY and OVER in the same query?

Not in the same SELECT level. Use subqueries or CTEs to combine: aggregate in inner query for summary, window function in outer query for context, or vice versa.

Show each employee with their salary and department average

SELECT

employee_name,

department,

salary,

AVG(salary) OVER (PARTITION BY department) AS dept_avg_salary

FROM employees;

Window function preserves all employee rows while adding department average context for comparison.

Create department summary with average and total salary

SELECT

department,

COUNT(*) AS employee_count,

AVG(salary) AS avg_salary,

SUM(salary) AS total_payroll

FROM employees

GROUP BY department;

GROUP BY collapses to summary rows. Use when you need aggregated totals without individual detail.