SQL SERVER – Beginning SQL 2012 – Aggregation Functions – Abstract from Joes 2 Pros Volume 2

This chapter is abstract from the Beginning SQL 2012 – Joes 2 Pros Volume 2.

You can get the Five part SQL Server 2012 Joes 2 Pros Combo Kit for complete reference.

Book On Amazon | Book On Flipkart

All supporting files are available with a free download from the www.Joes2Pros.com web site. This example is from the “SQL Queries 2012 Joes 2 Pros Volume 2” in the file SQLQueries2012Vol2Chapter5.1Setup.sql. If you need help setting up then look in the “Free Videos” section on Joes2Pros under “Getting Started” called “How to install your labs”

Aggregation Functions

Most people are familiar with aggregation using a GROUP BY with some of the fields using an aggregated function like SUM or COUNT. With GROUP BY you are limited to including only aggregated data in your result set. Of the two examples below we see the first query runs and gets a total of the amounts but the second query throws an error.

SELECT SUM(Amount) AS SumOfGrants
FROM [Grant]

SELECT *, SUM(Amount) 
FROM [Grant]
WHERE GrantID = '001'

Messages
Msg 8120, Level 16, State 1, Line 1
Column ‘Grant.GrantID’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

The OVER() clause

If we want to show the total amount next to every record of the table – or just one record of the table we can use the SUM aggregation function with OVER() instead of GROU P BY,

SELECT *, SUM(Amount) OVER()
FROM [Grant] 

Adding PARTITION BY to OVER()

Adding the OVER() clause allows us to see the total amount next to each grant record. OVER() applies the aggregation SUM(Amount) across all rows of a query. Here we have left the parentheses blank, which causes OVER() to apply the aggregation across all rows of each query. We could get the sub total for each employee. See how Employee 10 has the biggest grant of $41,000. If you look at Employee 7 who has three smaller grants but a total of more than $41,000. We can see this example where all of Employee 7’s individual grants are next to his total using the following code:

SELECT *, SUM(Amount) OVER(PARTITION BY EmpID)
FROM [Grant]

Market Share (Comparing individuals with totals)

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 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 1/5 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). The new column is added and confirms our prediction that Grant 007 represents just over 21% of all grants.

SELECT *, SUM(Amount) OVER() AS CompanyTotal, Amount / SUM(Amount) 
OVER() 
FROM [Grant]

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%. Add a * 100 to the expression to turn the ration into a percentage. To finish, give the column a descriptive title, PctOfTotal.

SELECT *,
 SUM(Amount) OVER() AS CompanyTotal,
 Amount / SUM(Amount) OVER() * 100 AS PctOfTotal
 FROM [Grant]

Book On Amazon | Book On Flipkart

Reference: Pinal Dave (https://blog.sqlauthority.com)