SQL Server PARTITION BY vs GROUP BY

Question

I use GROUP BY for all types of aggregate queries but in recent times I tried to reverse-engineer some code that uses PARTITION BY for performing aggregations. The PARTITION BY sounds very similar to GROUP BY. Can you explain the differences between the two and how to use them?

Answer 1

To understand the differences between GROUP BY and PARTITION BY, we can take an example:

Let us consider a table named Students with the following values:

id  firstname                   lastname                    mark
-------------------------------------------------------------------
1   abhay                        sahay                       50
2   akshay                       sharma                      55
3   rohit                        tiwari                      51
6   new                          tiwari                      57
1   abhay                        sahay                       55
1   abhay                        sahay                       59
2   akshay                       sharma                      59

GROUP BY

GROUP BY clause is used in conjunction with the aggregate functions

Syntax:

SELECT expression1, expression2, ... expression_n, 
       aggregate_function (aggregate_expression)
FROM tables
WHERE conditions
GROUP BY expression1, expression2, ... expression_n;

Applying GROUP BY in our previous table,

select SUM(mark)marksum,firstname from Students
group by id,firstname

Final Results:

marksum  firstname
----------------
114      akshay                      
164      abhay                     
57       new                      
51       rohit

In the  table we had 7 rows and after applying GROUP BY id, the number of rows reduced by adding up each row with the same name. That is it group each name with their marks and resulted in summing up the marks.

PARTITION BY

PARTITION BY does not reduce the number of rows that is returned.

We can apply PARTITION BY in our table:

SELECT SUM(mark) OVER (PARTITION BY id) AS marksum, firstname FROM Students

Result:

marksum firstname 
-------------------
164     abhay                     
164     abhay                     
164     abhay                     
114     akshay                      
114     akshay                      
51      rohit                   
57      new