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
http://online1.daytonastate.edu/player2.php?id=976abf49974d4686f87192efa0513ae0
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.
All Materials Copyright 2012 Dr. Ron Eaglin
Comments (0)
You don't have permission to comment on this page.