| 
  • If you are citizen of an European Union member nation, you may not use this service unless you are at least 16 years old.

  • You already know Dokkio is an AI-powered assistant to organize & manage your digital files & messages. Very soon, Dokkio will support Outlook as well as One Drive. Check it out today!

View
 

Lecture - Database and Aggregations

Page history last edited by Dr. Ron Eaglin 11 years ago

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.