What is the difference between PARTITION BY and GROUP BY in SQL server?

Question

I make use of GROUP BY in aggregate queries. Lately, I am performing reverse-engineering on some code that uses PARTITION BY to perform aggregations. I did research about PARTITION BY and found that there is a lot in GROUP BY, with some extra features embedded.  Please clarify my doubt.

Answer 1

GROUP BY actually modifies the entire query as shown below:

select customerId, count(*) as orderCount from Orders group by customerIdOn the other hand, PARTITION BY just works in a window functions like row_number:

select row_number() 

over (partition by customerId order by orderId as OrderNumberForThisCustomer from Orders

A GROUP BY generally minimizes 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 the way the window function's result is calculated.

If you want to get more insights into SQL, checkout this SQL Course from Intellipaat.