Aggregation Functions

Summary of Video

Demonstrates the use of aggregation functions GROUP BY, AVG, COUNT, ORDER BY, NTILE, and RANK.

Prerequisites

Lecture – Getting Started with Stored Functions in SQL Server

Video Link

Support Materials

Uses the Food Database for the first half

Uses the Crime Database for second half

USE CrimeDatabase
GO
/* Very Basic Aggreagation Query */
SELECT SUM(Robbery) FROM Cities
SELECT AVG(Robbery) FROM Cities
SELECT STDEV(Robbery) FROM Cities
SELECT AVG(Robbery) FROM Cities
WHERE [Population] > 1000
/* Demonstrates use of Rank function 
Query to show rank order of number of robberies
*/
SELECT City, Robbery,
   RANK() OVER (ORDER BY Robbery DESC) AS [Rank]
   FROM Cities  
   ORDER BY [Rank] ASC
/*
Does same query - but instead of numberical rank groups cities into quartiles using NTILE
*/
SELECT  City, Robbery,
   NTILE(4) OVER (ORDER BY Robbery) AS [Rank]
   FROM Cities  
   ORDER BY [Rank]
/*   I am adding a column to put quartile information into here */
ALTER TABLE Cities ADD RobberyQuartile INT
GO
/* This statement will place the quartile values for Robbery into the column I created */
WITH TiledItems AS (  
   SELECT City, 
   NTILE(4) OVER (ORDER BY Robbery DESC) as [Tile] 
   FROM Cities
)
UPDATE Cities  
   SET RobberyQuartile = CASE TiledItems.Tile  
     WHEN 1 THEN 1
     WHEN 2 THEN 2
     WHEN 3 THEN 3 
     WHEN 4 THEN 4
     END
   FROM Cities
   JOIN TiledItems ON TiledItems.City  = Cities.City    
GO

For the last SQL Statement here is some help

WITH TiledItems AS (  
    SELECT City, 
    NTILE(4) OVER (ORDER BY Robbery DESC) as [Tile] 
    FROM Cities
)

creates a temporary entity called TiledItems which is the results of a Subquery. The results of this Subquery are stored in TiledItems and are available to the remainder of the query because of the use of the WITH parameter.

CASE TiledItems.Tile
WHEN 1 THEN 1
WHEN 2 THEN 2
WHEN 3 THEN 3
WHEN 4 THEN 4

END

will return just like any other CASE statement (just like in C or C++). Unfortunately I have not found a way to directly return the value of TiledItems.Tile – maybe you can find a more direct method.

FROM Cities
   JOIN TiledItems ON TiledItems.City  = Cities.City

This last little bit ensures that the Table Join correctly joins the City in Cities with the City in the Subquery (TiledItems), otherwise the value will not be set to the correct City.

Note – the following query works as well without the CASE statement;

WITH TiledItems AS (  
    SELECT City, 
    NTILE(4) OVER (ORDER BY Robbery DESC) as [Tile] 
    FROM Cities
)
UPDATE Cities  
   SET RobberyQuartile = TiledItems.Tile  
   FROM Cities
   JOIN TiledItems ON TiledItems.City  = Cities.City

This is a good example of a complex query.