Aggregates with the Over Clause

You have likely heard the business term “Market Share”. If your company is the biggest and has sold 15 million units in an industry that has sold a total of 50 million units then your company’s market share is 30% (15/50 = .30). Market share represents your number divide by the sum of all other numbers. The two simple queries in the figure below show all the Grant table records and the sum of the grant amounts.

If we want to show the total amount next to every record of the table – or just one record of the table – SQL Server gives us the same error. It does not find the supporting aggregated language needed to support the SUM( ) aggregate function.

Adding the OVER( ) clause allows us to see the total amount next to each grant. We see 193,700 next to each record in the result set.

 

The sum of all 10 grants is $193,700. Recall the largest single grant (007) is $41,000. Doing the quick math in our head, we recognize $41,000 is around ¹/₅ of ~$200,000 and guesstimate that Grant 007 is just over 20% of the total.

Thanks to the OVER clause, there’s no need to guess. We can get the precise percentage. To accomplish this, we will add an expression that does the same math we did in our head. We want the new column to divide each grant amount by $193,700 (the total of all the grants).

By listing the total amount of all grants next to each individual grant, we automatically get a nice reference for how each individual grant compares to the total of all JProCo grants. The new column is added and confirms our prediction that Grant 007 represents just over 21% of all grants.

Notice that the figures in our new column appear as ratios. Percentages are 100 times the size of a ratio. Example:  the ratio 0.2116 represents a percentage of 21.16%. Multiplying a ratio by 100 will show the percentage. To finish, give the column a descriptive title, PercentOfTotal.