DEV Community

Kaziu
Kaziu

Posted on

Window Functions vs GROUP BY, Explained the Super Simple Way

What is a window function?

A window function is any function used with OVER (...).

SUM(amount) OVER (PARTITION BY name)
Enter fullscreen mode Exit fullscreen mode
  • The whole SUM(amount) OVER (...) is the window function
  • PARTITION BY goes inside it and says which groups to calculate over

PARTITION BY is optional. If you leave it out, the whole table is treated as one group.

Note: "Table partitioning" (CREATE TABLE ... PARTITION BY), which physically splits a table, uses the same keyword but is unrelated to window functions.

GROUP BY vs PARTITION BY

The short answer: this is the only difference.

  • GROUP BY: splits rows into groups and collapses each group into one row
  • PARTITION BY: splits rows into groups but keeps every row (each row gets the calculated value)

Sample table

sales table

name amount
Alice 100
Alice 200
Bob 500

With GROUP BY

SELECT name, SUM(amount)
FROM sales
GROUP BY name;
Enter fullscreen mode Exit fullscreen mode
name sum
Alice 300
Bob 500

Each name is collapsed into one row. The original "100" and "200" rows are gone.

With PARTITION BY

SELECT name, amount, SUM(amount) OVER (PARTITION BY name) AS total
FROM sales;
Enter fullscreen mode Exit fullscreen mode
name amount total
Alice 100 300
Alice 200 300
Bob 500 500

You still have 3 rows, and each row gets the total for its name.

Why is this useful?

Because the rows are kept, you can use the original value and the total in the same row.
For example, you can get "what share of this person's sales is this one sale" in a single query.

SELECT name, amount, amount / SUM(amount) OVER (PARTITION BY name) AS ratio
FROM sales;
Enter fullscreen mode Exit fullscreen mode
name amount ratio
Alice 100 0.33
Alice 200 0.67
Bob 500 1.00

ROW_NUMBER: numbering rows within each group

ROW_NUMBER() is a window function that numbers rows 1, 2, 3… within each group.
Adding ORDER BY inside OVER (...) sets the order within each group.

SELECT name, amount,
       ROW_NUMBER() OVER (PARTITION BY name ORDER BY amount DESC) AS row_num
FROM sales;
Enter fullscreen mode Exit fullscreen mode
name amount row_num
Alice 200 1
Alice 100 2
Bob 500 1

Each name's rows are numbered from the largest amount down.

Common pattern: get the top row of each group

Filter on row_num = 1 to get each person's highest sale.

SELECT * FROM (
  SELECT name, amount,
         ROW_NUMBER() OVER (PARTITION BY name ORDER BY amount DESC) AS row_num
  FROM sales
) t
WHERE row_num = 1;
Enter fullscreen mode Exit fullscreen mode
name amount row_num
Alice 200 1
Bob 500 1

row_num is created in the SELECT, so you can't use it in the WHERE of the same query. That's why it's wrapped in a subquery first.

ROW_NUMBER vs RANK

They differ in how they number ties.

amount ROW_NUMBER RANK
500 1 1
500 2 1
300 3 3
  • ROW_NUMBER: always gives a different number, even for ties (1, 2, 3)
  • RANK: gives ties the same number, then skips ahead (1, 1, 3)

Top comments (0)