SQL for Beginners: Window Functions vs GROUP BY

Windows function VS Group by
Both window functions and GROUP BY help you summarize data. But they do it in different ways, and mixing them up leads to confusing results.

1. GROUP BY squishes many rows into one row per group.
2. -A window function keeps every row, and just adds an extra column next to it.

Once you see that difference, it's easy to know which one to reach for.

We'll use one simple table the whole way through, so the examples stay easy to follow:

students
---------------------------
name      | class | score
---------------------------
Amina     | A     | 90
Brian     | A     | 70
Carla     | A     | 85
Dennis    | B     | 60
Efrem     | B     | 95

Difference between Windows Functions and Group by

GROUP BY answers a question like: "What's the average score in each class?" It gives you back fewer rows than you started with — one row per class.

A window function answers a question like: "How does this student's score compare to their class average?" It gives you back the same number of rows you started with — one per student — just with something extra calculated for each one.

So:

1. Want one summary row per group? Use GROUP BY.
2. Want to keep every row, but add a calculation? Use a window function.

Example 1: GROUP BY — one row per class

-- One row per class. We lose the individual students.
SELECT class, AVG(score) AS average_score
FROM students
GROUP BY class;

Result:

class | average_score
------------------------
A     | 81.6
B     | 77.5

Notice we no longer see Amina, Brian, or any individual name. GROUP BY traded the detail for a summary. That's fine when the summary is all you need.

Example 2: A window function — keep every row

Now say you want to see each student's score next to their class average, without losing any rows:

-- Every student stays, plus a new column showing their class average.
SELECT
    name,
    class,
    score,
    AVG(score) OVER (PARTITION BY class) AS class_average
FROM students;

Result:

name    | class | score | class_average
------------------------------------------
Amina   | A     | 90    | 81.6
Brian   | A     | 70    | 81.6
Carla   | A     | 85    | 81.6
Dennis  | B     | 60    | 77.5
Efrem   | B     | 95    | 77.5

All five students are still there. PARTITION BY class just tells SQL: "calculate the average separately for each class," instead of one big average for everyone.

Think of PARTITION BY as a GROUP BY that doesn't delete any rows.

Example 3: Ranking students in their class

Window functions are also great for ranking. Say you want to know each student's rank inside their own class:

-- Rank students by score, but restart the ranking for each class.
SELECT
    name,
    class,
    score,
    RANK() OVER (PARTITION BY class ORDER BY score DESC) AS class_rank
FROM students;

Result:

name    | class | score | class_rank
----------------------------------------
Amina   | A     | 90    | 1
Carla   | A     | 85    | 2
Brian   | A     | 70    | 3
Efrem   | B     | 95    | 1
Dennis  | B     | 60    | 2

ORDER BY score DESC says "rank from highest score to lowest." PARTITION BY class says "start the ranking over again for each class." That's why both Amina and Efrem get rank 1 — one for class A, one for class B.

Example 4: Comparing a row to the one before it

Another handy window function is LAG(). It looks at the previous row so you don't have to join a table to itself.

scores_by_month
--------------------
month  | score
--------------------
Jan    | 60
Feb    | 70
Mar    | 65

-- Show each month's score next to last month's score.
SELECT
    month,
    score,
    LAG(score) OVER (ORDER BY month) AS previous_month_score
FROM scores_by_month;

Result:

month | score | previous_month_score
----------------------------------------
Jan   | 60    | NULL
Feb   | 70    | 60
Mar   | 65    | 70

January has NULL because there's no month before it. Every other row simply grabs the score from the row above it.

When to use which — quick guide

What you want	Use
Just the average/total/count per group	GROUP BY
Individual rows AND a group calculation together	Window function
A running total	Window function
Ranking rows within a group	Window function
Comparing a row to the row before or after it	Window function

Simple mistakes to watch for

Trying to filter a window function with WHERE. This won't work:

-- This will cause an error.
SELECT name, class_rank
FROM students
WHERE class_rank <= 2;

WHERE runs before window functions are calculated, so it doesn't know what class_rank even is yet. Instead, wrap it in a subquery or CTE first, then filter on the outside:

WITH ranked AS (
    SELECT
        name,
        class,
        RANK() OVER (PARTITION BY class ORDER BY score DESC) AS class_rank
    FROM students
)
SELECT name, class, class_rank
FROM ranked
WHERE class_rank <= 2;

Forgetting ORDER BY inside OVER(). Without it, running totals won't build up properly — SQL won't know which order to add rows in.

Mixing up RANK() and ROW_NUMBER(). If two students tie for the same score, RANK() gives them the same number. ROW_NUMBER() always gives out different numbers, even for ties.

CONCLUSION

If you just need a summary, use GROUP BY. If you need to keep every row and add a calculation next to it, use a window function.