SQL for Beginners: Window Functions vs GROUP BY

Both window functions and GROUP BY can be used to summarize data in SQL. However, they work differently and choosing the wrong one can lead to confusing results. GROUP BY collapses multiple rows into a single row per group, while window functions maintain all original rows and add an extra calculated column.

For instance, if you want to know the average score per class, GROUP BY is the way to go. It will return fewer rows than your input, one for each class. In contrast, a window function will keep the same number of rows as your initial data, adding an extra column that provides additional calculations for each row.

Consider the following example using the "students" table:

- GROUP BY: SELECT class, AVG(score) AS average_score FROM students GROUP BY class;

Result: shows average scores per class, but without individual student names.

- Window function: SELECT name, class, score, AVG(score) OVER (PARTITION BY class) AS class_average FROM students;

Result: retains every student row while also displaying the class average alongside each student's score.

Similarly, ranking students within each class can be achieved using a window function (RANK()) OVER(PARTITION BY class ORDER BY score DESC) or by using GROUP BY in combination with another method.

However, there are some common pitfalls to be aware of when using window functions. For example, you cannot filter a window function directly using WHERE. Instead, you should use a subquery or Common Table Expression (CTE) to first calculate the window functions, and then filter the result.

Additionally, be cautious not to forget the ORDER BY clause inside the OVER() clause. Without it, running totals or other calculations might not build up properly, as SQL needs to know the order in which to process the rows.

Finally, remember that RANK() and ROW_NUMBER() serve different purposes. RANK() assigns the same rank to rows with equal values, whereas ROW_NUMBER() assigns unique ranks even for tied values.