-- Aggregate: Collapses to summary

SELECT

department,

AVG(salary) AS avg_salary,

COUNT(*) AS employee_count

FROM employees

GROUP BY department;

-- Window: Preserves detail

SELECT

employee_name,

department,

salary,

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

COUNT(*) OVER (PARTITION BY department) AS dept_employee_count

FROM employees;

-- Combining both

SELECT

department,

AVG(salary) AS dept_avg,

MAX(salary) - MIN(salary) AS salary_range

FROM employees

GROUP BY department

HAVING COUNT(*) > 10;

GROUP BY collapses rows into summaries

OVER preserves rows, adds context

GROUP BY requires all non-aggregate columns in GROUP BY

OVER allows any columns in SELECT

Cannot mix GROUP BY and OVER in same SELECT level

Use subqueries or CTEs to combine both

Core references in this topic include WHERE, =, <, >, <=, >=. Learn what each one does, when to use it, and the execution or engine rules that matter.

WHERE

Filters rows before projection and sorting. It decides which rows continue through the query pipeline.

SELECT ... FROM table WHERE condition;

Most performance issues start with a weak WHERE clause or a missing supporting index.

=

Returns rows where the left and right values are exactly equal.

column = value

Use with exact matches. Do not use = NULL.

<, >, <=, >=

Range comparison operators for less-than, greater-than, and inclusive boundary checks.