{"id":933,"date":"2011-09-20T21:34:16","date_gmt":"2011-09-20T21:34:16","guid":{"rendered":"http:\/\/craftydba.com\/?p=933"},"modified":"2011-09-26T14:44:37","modified_gmt":"2011-09-26T14:44:37","slug":"selecting-data-part-3","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=933","title":{"rendered":"Selecting Data – Part 3"},"content":{"rendered":"

The SELECT<\/a> reserved word is part of the Data Manipulation Language (DML) defined by Codd<\/a> and is used to query data from the database. Because of its popularity, there are many clauses and functions that can be used to construct a query to retrieve data.<\/p>\n

Today, I am continuing our exploration by reviewing SELECT queries that aggregate data. Data aggregation <\/a>is the process of converting many records into a few records with special meaning. Many of the functions <\/a>used in aggregation have statistical meaning such as minimum value, maximum value, etc.<\/p>\n

I will be using the Adventure Works <\/a>Sample database supplied by Microsoft during this talk.<\/p>\n

We will start by looking at the the sales people and their year-to-date sales numbers. The following query joins the SalesPerson and Person tables to retrieve our results.<\/p>\n

-- Use the correct database\r\nUSE AdventureWorks2008R2\r\nGO\r\n\r\n-- List existing sales force\r\nSELECT \r\n    P.BusinessEntityID as Id, \r\n    cast(P.LastName as char(20)) as LastName, \r\n    cast(P.FirstName as char(20)) as FirstName, \r\n    Str(S.SalesYTD, 12, 2) as SalesYtd\r\nFROM Sales.SalesPerson S INNER JOIN Person.Person P\r\nON s.BusinessEntityID = P.BusinessEntityID\r\nWHERE TerritoryID IS NOT NULL\r\nGO\r\n\r\n-- Output\r\nId          LastName             FirstName            SalesYtd\r\n----------- -------------------- -------------------- ------------\r\n275         Blythe               Michael                3763178.18\r\n276         Mitchell             Linda                  4251368.55\r\n277         Carson               Jillian                3189418.37\r\n278         Vargas               Garrett                1453719.47\r\n279         Reiter               Tsvi                   2315185.61\r\n280         Ansman-Wolfe         Pamela                 1352577.13\r\n281         Ito                  Shu                    2458535.62\r\n282         Saraiva              Jos\u00e9                   2604540.72\r\n283         Campbell             David                  1573012.94\r\n284         Mensa-Annan          Tete                   1576562.20\r\n286         Tsoflias             Lynn                   1421810.92\r\n288         Valdez               Rachel                 1827066.71\r\n289         Pak                  Jae                    4116871.23\r\n290         Varkey Chudukatil    Ranjit                 3121616.32\r\n<\/span><\/pre>\n<\/p>\n
One nice feature of Microsoft Access is the FIRST<\/a> and LAST <\/a>functions are part of the aggregation set. We can recreate these features by using the ORDER BY<\/a> and TOP <\/a>clauses. The queries below return the first and last sales person by total sales.<\/p>\n
-- Top sales person (LAST)\r\nSELECT TOP 1 \r\n    P.BusinessEntityID as Id,\r\n    cast(P.LastName as char(20)) as LastName,\r\n    cast(P.FirstName as char(20)) as FirstName,\r\n    Str(S.SalesYTD, 12, 2) as SalesYtd\r\nFROM Sales.SalesPerson S INNER JOIN Person.Person P ON s.BusinessEntityID = P.BusinessEntityID\r\nWHERE TerritoryID IS NOT NULL\r\nORDER BY SalesYTD DESC\r\n\r\n-- Output\r\nId          LastName             FirstName            SalesYtd\r\n----------- -------------------- -------------------- ------------\r\n276         Mitchell             Linda                  4251368.55\r\n\r\n-- Bottom sales person (FIRST)\r\nSELECT TOP 1 \r\n    P.BusinessEntityID as Id,\r\n    cast(P.LastName as char(20)) as LastName,\r\n    cast(P.FirstName as char(20)) as FirstName,\r\n    Str(S.SalesYTD, 12, 2) as SalesYtd\r\nFROM Sales.SalesPerson S INNER JOIN Person.Person P ON s.BusinessEntityID = P.BusinessEntityID\r\nWHERE TerritoryID IS NOT NULL\r\nORDER BY SalesYTD ASC\r\n\r\n-- Output\r\nId          LastName             FirstName            SalesYtd\r\n----------- -------------------- -------------------- ------------\r\n280         Ansman-Wolfe         Pamela                 1352577.13\r\n<\/span><\/pre>\n<\/p>\n
The power of aggregation is achieved by using the GROUP BY <\/a>and HAVING <\/a>clauses. Basically, GROUP BY takes all values that are the same for a given column list and returns one unique row. The HAVING clause is a secondary filter like the WHERE clause that can specify some aggregate condition needs to be meet.<\/p>\n
The query below returns the number of sales people by country. The COUNT <\/a>function returns a integer value.<\/p>\n
-- Count sales people by country\r\nSELECT T.CountryRegionCode AS Country, COUNT(*) AS SalesPeople\r\nFROM Sales.SalesPerson P INNER JOIN Sales.SalesTerritory T ON P.TerritoryID = T.TerritoryID\r\nGROUP BY T.CountryRegionCode\r\n\r\n-- Output\r\nCountry SalesPeople\r\n------- -----------\r\nAU      1\r\nCA      2\r\nDE      1\r\nFR      1\r\nGB      1\r\nUS      8 \r\n<\/span><\/pre>\n<\/p>\n
We can rewite this query with a HAVING clause to return all countries that have multiple sales people.<\/p>\n
-- -- Show countries that have multiple sales people\r\nSELECT T.CountryRegionCode AS Country, COUNT(*) AS SalesPeople\r\nFROM Sales.SalesPerson P INNER JOIN Sales.SalesTerritory T ON P.TerritoryID = T.TerritoryID\r\nGROUP BY T.CountryRegionCode\r\nHAVING COUNT(*) > 1\r\n\r\n--Output\r\nCountry SalesPeople\r\n------- -----------\r\nCA      2\r\nUS      8\r\n<\/span><\/pre>\n<\/p>\n
Common day statistics such as MIN<\/a>, MAX <\/a>and AVG <\/a>can be calculated by selecting the correct function.  The example below shows the minimum, maximum, and average bonus for a sales person in each country.<\/p>\n
-- Show the min, max, & avg bonus by country\r\nSELECT \r\n    T.CountryRegionCode AS Country, \r\n    MIN(P.Bonus) AS SmallBonus, \r\n    MAX(P.Bonus) AS BigBonus, \r\n    AVG(P.Bonus) AS AvgBonus\r\nFROM Sales.SalesPerson P INNER JOIN Sales.SalesTerritory T ON P.TerritoryID = T.TerritoryID\r\nGROUP BY T.CountryRegionCode\r\n\r\n-- Output\r\nCountry SmallBonus            BigBonus              AvgBonus\r\n------- --------------------- --------------------- ---------------------\r\nAU      5650.00               5650.00               5650.00\r\nCA      500.00                5000.00               2750.00\r\nDE      75.00                 75.00                 75.00\r\nFR      985.00                985.00                985.00\r\nGB      5150.00               5150.00               5150.00\r\nUS      2000.00               6700.00               3906.25\r\n<\/span><\/pre>\n<\/p>\n
The SUM <\/a>function can be used to totaled up all values in a particular column. The example below looks at the average and total sales by country regardless of person.<\/p>\n
-- Show avg and total sales by sales person for a particular country\r\nSELECT \r\n    T.CountryRegionCode AS TheCountry, \r\n    AVG(P.SalesYTD) AS TheAvg, \r\n    SUM(P.SalesYTD) AS TheSales\r\nFROM Sales.SalesPerson P INNER JOIN Sales.SalesTerritory T ON P.TerritoryID = T.TerritoryID\r\nGROUP BY T.CountryRegionCode\r\n\r\n-- Output\r\nTheCountry TheAvg                TheSales\r\n---------- --------------------- ---------------------\r\nAU         1421810.9242          1421810.9242\r\nCA         2029130.0912          4058260.1825\r\nDE         1827066.7118          1827066.7118\r\nFR         3121616.3202          3121616.3202\r\nGB         4116871.2277          4116871.2277\r\nUS         2559979.8237          20479838.5899\r\n<\/span><\/pre>\n<\/p>\n
Last but not least, advance statistics such as standard deviation using the STDEV <\/a>function or variance using the VAR <\/a>fucntion can be calculated. The example below goes to the Sales Order Header level and returns statistics by country for the
\n2008 year.<\/p>\n
-- Get statistics on sales detail by country for 2008\r\nSELECT\r\n    YEAR(H.OrderDate) AS TheYear, \r\n    T.CountryRegionCode AS TheCountry, \r\n    STR(AVG(H.TotalDue), 12, 2) AS TheAvg, \r\n    STR(STDEV(H.TotalDue), 12, 3) AS TheStdDev, \r\n    STR(VAR(H.TotalDue), 12, 3) AS TheVar\r\nFROM Sales.SalesOrderHeader H INNER JOIN Sales.SalesTerritory T ON H.TerritoryID = T.TerritoryID\r\nWHERE YEAR(H.OrderDate) = 2008\r\nGROUP BY YEAR(H.OrderDate), T.CountryRegionCode\r\nORDER BY YEAR(H.OrderDate), T.CountryRegionCode\r\n\r\n-- Output\r\nTheYear     TheCountry TheAvg       TheStdDev    TheVar\r\n----------- ---------- ------------ ------------ ------------\r\n2008        AU              1254.82     3111.270  9680003.033\r\n2008        CA              1800.43     7957.318 63318908.399\r\n2008        DE              1764.02     6209.980 38563854.668\r\n2008        FR              2110.00     8906.554 79326696.856\r\n2008        GB              1888.58     7335.738 53813050.772\r\n2008        US              2748.81     9902.666 98062789.479\r\n<\/span><\/pre>\n<\/p>\n
In summary, aggregation is a great way to summarize data for organizational decision makers.
\nThe following functions are part of TSQL aggregation <\/a>with the except of FIRST and LAST which can be
\nemulated.<\/p>\n