SQLTeam.com | Weblogs | Forums

Partition By vs Group By


#1

Are there Pros or Cons using Partition By vs Group By? I realized that Aggregate, Ranking and Value functions can be done with Partition By, returning the same results as Group By.


#2

Partition by and group by serve different purposes. See this example. When you use group by you get one row per group. Any other detail such as order date you may want to see is not available. Partition by gives you the details without grouping the other columns.

Another way to think about it is that if you use group by, any column in your select list must either be in the group by clause, or must be within an aggregate function.

CREATE TABLE #CustomerOrders (
	CustomerId INT
	,DATE DATE
	,Quantity INT
	);

INSERT INTO #CustomerOrders
VALUES 
(1, '20190102', 5),
(1, '20190102', 7),
(2, '20190103', 2),
(2, '20190104', 4);


SELECT *
	,SUM(Quantity) OVER (PARTITION BY CustomerId) AS CUstomerTotal
FROM #CustomerOrders;

SELECT CustomerId
	,SUM(Quantity)
FROM #CustomerOrders
GROUP BY CustomerId;