SQL Server Difference between PARTITION BY and GROUP BY

0 votes
I've been using GROUP BY for all types of aggregate queries over the years. Recently, I've been reverse-engineering some code that uses PARTITION BY to perform aggregations. In reading through all the documentation I can find about PARTITION BY, it sounds a lot like GROUP BY, maybe with a little extra functionality added in?
Jun 20 in Others by polo
• 1,500 points
82 views

1 answer to this question.

0 votes

We can take a simple example.

Consider a table named TableA with the following values:

id  firstname                   lastname                    Mark
-------------------------------------------------------------------
1   arun                        prasanth                    40
2   ann                         antony                      45
3   sruthy                      abc                         41
6   new                         abc                         47
1   arun                        prasanth                    45
1   arun                        prasanth                    49
2   ann                         antony                      49

GROUP BY

The SQL GROUP BY clause can be used in a SELECT statement to collect data across multiple records and group the results by one or more columns.

In more simple words GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

Syntax:

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

We can apply GROUP BY in our table:

select SUM(Mark)marksum,firstname from TableA
group by id,firstName

Results:

marksum  firstname
----------------
94      ann                      
134     arun                     
47      new                      
41      sruthy   

In our real table we have 7 rows and when we apply GROUP BY id, the server group the results based on id:

In simple words:

here GROUP BY normally reduces the number of rows returned by rolling them up and calculating Sum() for each row.

PARTITION BY

Before going to PARTITION BY, let us look at the OVER clause:

According to the MSDN definition:

OVER clause defines a window or user-specified set of rows within a query result set. A window function then computes a value for each row in the window. You can use the OVER clause with functions to compute aggregated values such as moving averages, cumulative aggregates, running totals, or a top N per group results.

PARTITION BY will not reduce the number of rows returned.

We can apply PARTITION BY in our example table:

SELECT SUM(Mark) OVER (PARTITION BY id) AS marksum, firstname FROM TableA

Result:

marksum firstname 
-------------------
134     arun                     
134     arun                     
134     arun                     
94      ann                      
94      ann                      
41      sruthy                   
47      new  

Look at the results - it will partition the rows and returns all rows, unlike GROUP BY.

answered Jun 21 by nisha
• 2,180 points

Related Questions In Others

0 votes
1 answer

What is the difference between hadoop and google analytics ?

I will try and answer this as ...READ MORE

answered Aug 22, 2018 in Others by Frankie
• 9,830 points
817 views
0 votes
0 answers

Mention the difference between Data Driven Testing and Retesting?

Retesting:  It is a process of checking ...READ MORE

Feb 1, 2019 in Others by riya

closed Feb 1, 2019 by Omkar 1,574 views
+1 vote
1 answer

What is the difference between Dark Web and Deep Web?

The content that which you cannot find ...READ MORE

answered Feb 6, 2019 in Others by Priyaj
• 58,080 points
409 views
0 votes
1 answer

What is the difference between BASH and DOS?

The major difference between the BASH and ...READ MORE

answered Mar 8, 2019 in Others by Nabarupa
10,128 views
0 votes
1 answer

SQL Server: PARTITION BY vs GROUP BY

To understand the differences between GROUP BY ...READ MORE

answered Feb 9 in Database by Neha
• 8,920 points
517 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
115 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
41 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,920 points
211 views
0 votes
1 answer

Ordering by the order of values in a SQL IN() clause

Use MySQL FIND_IN_SET function: SELECT * ...READ MORE

answered Jun 6 in Others by nisha
• 2,180 points
39 views
0 votes
1 answer

SQL Server replaces LEFT JOIN for LEFT OUTER JOIN in view query

there are different ways of writing joins. ...READ MORE

answered Jun 9 in Others by nisha
• 2,180 points
54 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP