SQL Server: Difference between PARTITION BY and GROUP BY

標籤：wiki   odi   dia   pos   group   href   org   over   rom   

https://stackoverflow.com/questions/2404565/sql-server-difference-between-partition-by-and-group-by

 

They‘re used in different places. group by modifies the entire query, like:

select customerId, count(*) as orderCountfrom Ordersgroup by customerId

But partition by just works on a window function, like row_number:

select row_number() over (partition by customerId order by orderId)    as OrderNumberForThisCustomerfrom Orders

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.

SQL Server: Difference between PARTITION BY and GROUP BY