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. GROUP BY squishes many rows into one row per group. -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: Want one summary row per group? Use GROUP BY . 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 | 8