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]
Messages
193700.00

1 rows

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.

0 rows

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]
  GrantID GrantName EmpID Amount CompanyTotal PctOfTotal
1 001 92 Purr_Scents %% team 7 4750.00 193700.00 2.45
2 002 K_Land fund trust 2 15750.00 193700.00 8.13
3 003 Robert@BigStarBank.com 7 18100.00 193700.00 9.34
4 004 Norman’s Outreach NULL 21000.00 193700.00 10.84
5 005 BIG 6′s Foundation% 4 21000.00 193700.00 10.84
6 006 TALTA_Kishan Internatio… 3 18100.00 193700.00 9.34
7 007 Ben@MoreTechnology.com 10 41000.00 193700.00 21.16

 

 

 

 

 

 

10 rows

Book On Amazon | Book On Flipkart

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

11 thoughts on “SQL SERVER – Beginning SQL 2012 – Aggregation Functions – Abstract from Joes 2 Pros Volume 2

  1. Sir / pinaldave ji Is there any way to send a message from sql server to a application(developed in C#) when a table in sql server is updated? if, yes, You are requested to let me know the code of T-sql and C#. regards Kaushik +919267711770

  2. I was even not able to write a sql with simple join but with the help of this site i learn a lots of things on sql …..Really helpful pinal thanks a lot….keep going….:)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s