SQL: Summarizing and Grouping Data



http://www.flickr.com/photos/konnecke/922432859/

Data Summarization

SQL has aggregate functions that allow you to summarize large volumes of data. Summarizing data helps to determine trends. Many times data needs to be summarized with reports. Aggregate functions

take the values of multiple rows grouped together, i.e., values stored in a column, and returns a single value. 

Common aggregate Microsoft SQL functions:

AVG(): Returns the average of data stored in a column

COUNT(*):  Counts the rows in a table (including NULL values)

COUNT (Column name):  Counts the rows in a column (excluding Null values)

FIRST():  Returns the first value stored in a column

LAST():  Returns the last value stored in a column

MAX():  Returns the highest value stored in a column

MIN():  Returns the lowest value stored in a column

SUM():  Returns the sum of values stored in a column

Service Plans

SvcID	SvcName	SvcDesc	SvcPrice	DiscountPrice
1	TV	TV Plan	$64.99	$59.99
2	Phone	Home Phone	$44.99	$39.99
3	Internet	Internet Access	$54.99	$49.99
4	Bundle	Internet, Router, Unlimited Calling	$79.99	$74.99

Create query to find the highest price and the highest discounted price:

SELECT MAX (SvcPrice) AS HighestPrice, MAX (DiscountPrice) AS HighestDiscountPrice
FROM [Service Plans];

QryHighPrice

HighestPrice	HighestDiscountPrice
$79.99	$74.99

 

Create query to find the lowest price and lowest discounted price:

SELECT MIN (SvcPrice) AS LowestPrice, MIN (DiscountPrice) AS LowestDiscountPrice
FROM [Service Plans];

QryLowPrice

LowestPrice	LowestDiscountPrice
$44.99	$39.99



Create query to find the average price and average discounted price:

SELECT AVG (SvcPrice) AS AvgPrice, AVG (DiscountPrice) AS AvgDiscountPrice
FROM [Service Plans};

QryAvgPriceandDiscountPrice

AvgPrice	AvgPriceDiscountPrice
$61.24	$56.24

 

 

Microsoft does not support the DISTINCT function within most functions.

The SELECT statement allows you to use multiple aggregate functions.

Table Creation

Create a table to track customer orders:

Orders

PlanID	OrderDate	CustID
D1002	11/11/1999	4
D1002	11/11/1999	5
w1001	3/15/2001	5
l2003	3/15/2010	6
W1001	3/15/2001	6
L2003	3/15/2010	7
l1001	3/20/2001	8
l2002	7/10/2000	9
l2001	7/10/2000	10
l2004	7/15/2001	11
l2001	7/15/2001	12
L1001	6/1/1999	13
D1101	11/4/2000	14
D1001	11/4/2000	15
D1002	11/4/2000	16
l2001	5/13/2000	17
l2004	5/14/2001	18
I2004	5/15/2001	19
I2001	5/14/2001	20
i2001	5/14/2001	21
12002	5/15/2001	21

Creating Groups

GROUP BY: The GROUP BY clause is is used with aggregate functions to combine groups or rows of data into a single record.

Every keyword specified in the SELECT keyword must also be used in the GROUP BY clause.

Create a query to find the total number of service plans for customers 5 and 6 and then group the results:

SELECT CustID, COUNT (*) AS TotalCount
FROM Orders
WHERE CustID IN (5,6)
GROUP BY CustID;

QryTotalCount

CustID	TotalCount
5	2
6	2

Create a query to find the last order date for customers 5 and 6 and group the results:

SELECT CustID, MAX (OrderDate) AS LastOrder
FROM Orders
WHERE CustID IN 5,6)
GROUP BY CustID;

QryLastOrderDt

CustID	LastOrder
5	3/15/2001
6	3/15/2010

Filtering Groups

HAVING: The HAVING clause is used with the GROUP BY clause to filter groups of data (instead of rows of data) using aggregate functions, such as AVG (), COUNT (), MAX (), MIN (), SUM (). The HAVING clause must be placed after the GROUP BY clause. All column names specified after the SELECT keyword, must be be present in the GROUP BY clause.

Create a query to list customers with at least two service plans:

SELECT CustID, COUNT (*) AS SvcPlans

FROM Orders

GROUP BY CustID

HAVING COUNT (*) >=2;

QrySvcPlans

CustID	SvcPlans
5	2
6	2
21	2

Create a query to find a customers 5 and 6 first OrderDate:

SELECT CustID, MIN (OrderDate) AS FirstOrderDate
FROM Orders
GROUP BY CustID
HAVING CustID IN (5,6);

QryFirstOrderDate

CustID	FirstOrderDate
5	11/11/1999
6	3/15/2001



Create a query to find the total number of service plans for customers 5 and 6:

SELECT CustID, COUNT (*) AS TotalCount
FROM Orders
GROUP BY CustID
HAVING CustID IN (5,6);

QryTotalCount

CustID	TotalCount
5	2
6	2

http://en.wikipedia.org/wiki/Aggregate_function

http://databases.about.com/od/sql/l/aaaggregate1.htm

http://msdn.microsoft.com/en-us/library/ms173454.aspx