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 in Database by Vaani
• 7,020 points
789 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 by Neha
• 8,940 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 in Database by Kithuzzz
• 20,660 points
76 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 in Database by Vaani
• 7,020 points
54 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 in Database by Kithuzzz
• 20,660 points
81 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 in Database by Kithuzzz
• 20,660 points
281 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 in Others by nisha
• 2,190 points
213 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 in Database by Vaani
• 7,020 points
167 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 in Database by Vaani
• 7,020 points
71 views
0 votes
1 answer

What is a stored procedure?

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

answered Feb 4 in Database by Neha
• 8,940 points
292 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 in Database by Neha
• 8,940 points
276 views
0 votes
1 answer

Function vs. Stored Procedure in SQL Server

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

answered Feb 17 in Database by Neha
• 8,940 points

edited Feb 17 by Neha 821 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