"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

Let's 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

Let's run a group by statement to have a quick review.

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


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

Let's 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


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.
Let's modify the query to:

        sum(itemcount) over(partition by itemname) as totalcount,
         ItemCount*1.0/sum(itemcount) over(partition by itemname) as percentcomposition


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

Used ' 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.

Let's 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's rank the items by percent composition  per group. Using 'group by'? No, using this way:

   row_number() over(partition by itemname order by itemcount) as RankByGroupAsc,
  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