Window function vs GROUP BY mental model in SQL

Introduction

Window functions and GROUP BY help summarize data, but window functions keep all rows while GROUP BY groups rows into one result each.

When you want to calculate totals but still see each individual row.

When you need to rank or number rows without losing detail.

When you want to compare each row to a group summary.

When you want to find running totals or moving averages.

When you want to group data and get one result per group.

Syntax

SQL

SELECT column1, aggregate_function(column2) OVER (PARTITION BY column3 ORDER BY column4) AS window_result
FROM table;

-- vs --

SELECT column1, aggregate_function(column2) AS group_result
FROM table
GROUP BY column1;

Window functions use OVER() to define how to group and order rows but keep all rows in the output.

GROUP BY collapses rows into one per group, so you lose individual row details.

Examples

Shows total sales per department on every employee row without grouping rows.

SQL

SELECT employee, department, SUM(sales) OVER (PARTITION BY department) AS dept_sales
FROM sales_data;

Shows one row per department with total sales, grouping rows together.

SQL

SELECT department, SUM(sales) AS dept_sales
FROM sales_data
GROUP BY department;

Ranks employees by sales but keeps all employee rows.

SQL

SELECT employee, sales, RANK() OVER (ORDER BY sales DESC) AS sales_rank
FROM sales_data;

Sample Program

First query shows each employee's sales and total sales for their department on every row. Second query shows one row per department with total sales.

SQL

CREATE TABLE sales_data (
  employee VARCHAR(20),
  department VARCHAR(20),
  sales INT
);

INSERT INTO sales_data VALUES
('Alice', 'Electronics', 100),
('Bob', 'Electronics', 150),
('Carol', 'Clothing', 200),
('Dave', 'Clothing', 50);

-- Window function example
SELECT employee, department, sales,
       SUM(sales) OVER (PARTITION BY department) AS dept_total_sales
FROM sales_data
ORDER BY department, employee;

-- GROUP BY example
SELECT department, SUM(sales) AS dept_total_sales
FROM sales_data
GROUP BY department
ORDER BY department;

Important Notes

Window functions keep all rows and add summary info alongside each row.

GROUP BY reduces rows to one per group, losing individual row details.

Use window functions when you want both detail and summary together.

Summary

Window functions add summary data without hiding individual rows.

GROUP BY groups rows and shows one result per group.

Choose window functions to keep detail and get group info at once.