Difference between PARTITION BY and GROUP BY

Question

Post reply

Difference between PARTITION BY and GROUP BY

Santosh Bandhu

SSC Veteran

Points: 243
More actions
May 4, 2010 at 5:45 am

#386843

Hi,
I recently came across use of PARTITION BY with Aggregate functions. What I read I can use PARTITION BY clause with aggregate functions to more or less achieve similar result set as with using GROUP BY clause (probably event better).
So, I am trying to find out what is the difference between PARTITION BY and GROUP BY? And which one is better performance wise?
Regards,
Santosh

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

PARTITION BY is used in windowed aggregates, while GROUP BY is used in regular aggregates.

Look up OVER in BOL to find out more.

-- Gianluca Sartori

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 2

Better , in terms of performance , shouldn't enter into it as the do different things

GROUP by is for use by aggregate functions.

Ie

Select id,sum(costs)

from yourtab

group by Id

Partition by is for use with ranking functions

Select id,count(*) over (partition by id order by 1)

from yourtab

But a partition by can also be used in an aggregate function

Select id,sum(costs) over (partition by id )

from yourtab

Clear Sky SQL
My Blog[/url]

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 3

Gianluca Sartori (5/4/2010)
PARTITION BY is used in windowed aggregates.

can you give any example ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

Santosh Bandhu (5/4/2010)
What I read I can use PARTITION BY clause with aggregate functions to more or less achieve similar result set as with using GROUP BY clause (probably event better).

both work similarly there performance depend on kind of data/indexes used etc.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

spaghettidba SSC Guru Points: 105732 More actions · Answer 5

Bhuvnesh (5/4/2010)
Gianluca Sartori (5/4/2010)
PARTITION BY is used in windowed aggregates.
can you give any example ?

Dave put a neat example in his post, last query.

-- Gianluca Sartori

Difference between PARTITION BY and GROUP BY

Cookies on SQLServerCentral