SQL Server PARTITION BY vs GROUP BY

0 votes
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?
Feb 9, 2022 in Database by Vaani
• 7,070 points
2,544 views

1 answer to this question.

0 votes

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  

answered Feb 9, 2022 by Neha
• 9,060 points

Related Questions In Database

0 votes
0 answers

Execution sequence of Group By, Having and Where clause in SQL Server?

I'm just not sure how a SQL ...READ MORE

Aug 19, 2022 in Database by Kithuzzz
• 38,020 points
533 views
0 votes
1 answer

SQL Group By with an Order By

In any version of MySQL, you can use the aggregate ...READ MORE

answered Feb 10, 2022 in Database by Vaani
• 7,070 points
497 views
0 votes
0 answers

DateTime2 vs DateTime in SQL Server

Which one: datetime datetime2 is the recommended way to store date and ...READ MORE

Aug 14, 2022 in Database by Kithuzzz
• 38,020 points
519 views
0 votes
0 answers

How to group by month from Date field using sql

How can I only group by month ...READ MORE

Aug 20, 2022 in Database by Kithuzzz
• 38,020 points
945 views
0 votes
1 answer

SQL Server: Difference between PARTITION BY and GROUP BY

We can take a simple example. Consider a ...READ MORE

answered Jun 21, 2022 in Others by nisha
• 2,210 points
1,072 views
0 votes
1 answer

How do I UPDATE from a SELECT in SQL Server?

MERGE INTO YourTable T USING ...READ MORE

answered Feb 3, 2022 in Database by Vaani
• 7,070 points
771 views
0 votes
0 answers

How do I UPDATE from a SELECT in SQL Server?

INSERT INTO Table (col1, col2, col3) SELECT col1, ...READ MORE

Feb 4, 2022 in Database by Vaani
• 7,070 points
469 views
0 votes
1 answer

What is a stored procedure?

A stored procedure is a set of ...READ MORE

answered Feb 4, 2022 in Database by Neha
• 9,060 points
997 views
0 votes
1 answer

LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

At the top level there are mainly ...READ MORE

answered Feb 4, 2022 in Database by Neha
• 9,060 points
1,939 views
0 votes
1 answer

Function vs. Stored Procedure in SQL Server

Functions are calculated values that cannot make ...READ MORE

answered Feb 17, 2022 in Database by Neha
• 9,060 points

edited Feb 17, 2022 by Neha 19,149 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP