Introduction

"Over" clause was introduced in SQL server 2005 but aside from knowing that this keyword is used
with aggregates up to until that SQL server keyword still
sounds Greek to me.  If you are like me then might as well enjoy reading this article.
Microsoft official guide for "over" clause can be found on this Technet
link.



Case Study.

Lets have an inventory table for a case study.  The table will consist inventory items which
can be grouped by inventory type, color, size and inventory count.

Sample Data


Here's the Sample Data.


Figure 1. Sample  data. (Click image to enlarge.)


"Group By" a review

Lets run a group by statement to have a quick review.

select itemname, sum(itemcount) as totalcount
 from Items group by itemname

result

itemname         totalcount
----------          ------------
ArmChair               30
RockingChair         15

Lets make use of the "over" clause

Let's make use of the over clause for the first time. The same query without the group by.

 select itemname,sum(itemcount) over(partition by itemname) as totalcount
 from Items

 Result

itemname         totalcount
-----------        ------------
ArmChair             30
ArmChair             30
ArmChair             30
RockingChair       15
RockingChair       15
RockingChair       15

Note that the sum aggregate was assigned to each item in the group

Let's Look for an opportunity on how to use the data

The result above assigned the total aggregate to each of the group member. Maybe
We can make use of it to see what is the percent composition of each element in the group.
Yeah, percent composition sound a lot like chemistry.
Lets modify the query to:

 select 
         itemname,
         Itemsize,
         Itemcount, 
        sum(itemcount) over(partition by itemname) as totalcount,
         ItemCount*1.0/sum(itemcount) over(partition by itemname) as percentcomposition
 from 
         Items

Results

itemname     Itemsize     Itemcount     totalcount percentcomposition
----------    --------       ----------      ---------    ------------------
ArmChair     Small             5                 30             0.166666666666
ArmChair     Medium         10               30             0.333333333333
ArmChair     Large             15               30             0.500000000000
RockingChair Small             5               15             0.333333333333
RockingChair Medium         2               15             0.133333333333
RockingChair Large             8                15            0.533333333333

note
I use ' ItemCount*1.0'  so that sql server will return the decimal places otherwise
it will return an integer type with value of 0

Going Back to Group by clause.


Lets go back to where we have started - the group by clause.  If you are going to
write this query all over again using group by it will be very painful.

Let's Rank the items by percent composition per group.

Let rank the items by percent composition  per group. Using 'group by'? No, using this way:

 select 
   row_number() over(partition by itemname order by itemcount) as RankByGroupAsc,
  itemname,
  Itemsize,
  Itemcount,
  sum(itemcount) over(partition by itemname) as totalcount,
  ItemCount*1.0/sum(itemcount) over(partition by itemname) as percentcomposition
  from Items
  order by 2


Result to.

RankByGroupAsc    itemname    Itemsize    Itemcount    totalcount    percentcomposition
-----------------      -----------    ---------   ----------     -----------     ---------------------
1                                ArmChair       Small          5                   30                   0.166666666666
2                               ArmChair        Medium     10                  30                  0.333333333333
3                               ArmChair         Large          15                  30                  0.500000000000
1                               RockingChair  Medium     2                    15                  0.133333333333
2                               RockingChair   Small         5                     15                 0.333333333333
3                              RockingChair    Large         8                     15                 0.533333333333