GROUP BY Vs PARTITION BY in SQL SERVER

We can take a simple example

we have 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 GroupBy 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

befre going to partition by

just look at Over clause

As per MSDN defenition

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 affect the number of rows returned

we can apply partition by in our example

select SUM(Mark) OVER (PARTITION BY id) AS Cost,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 results all rows not like group by.     

Check my stack overflow post related to this topic : LINK