I've been using GROUP BY
for all types of aggregate queries over the years. Recently, I've been reverse-engineering some code that uses PARTITION BY
to perform aggregations. In reading through all the documentation I can find about PARTITION BY
, it sounds a lot like GROUP BY
, maybe with a little extra functionality added in? Are they two versions of the same general functionality, or are they something different entirely?
Sql-server – SQL Server: Difference between PARTITION BY and GROUP BY
aggregate-functionssql servertsqlwindow-functions
Related Topic
- Sql-server – How to check if a column exists in a SQL Server table
- Sql-server – the difference between varchar and nvarchar
- Sql-server – Check if table exists in SQL Server
- Sql-server – LEFT JOIN vs. LEFT OUTER JOIN in SQL Server
- Sql – Difference between JOIN and INNER JOIN
- Sql – How to do an UPDATE statement with JOIN in SQL Server
- Sql – How to UPDATE from a SELECT in SQL Server
- Sql – Find all tables containing column with specified name – MS SQL Server
Best Answer
They're used in different places.
group by
modifies the entire query, like:But
partition by
just works on a window function, likerow_number
:A
group by
normally reduces the number of rows returned by rolling them up and calculating averages or sums for each row.partition by
does not affect the number of rows returned, but it changes how a window function's result is calculated.