Aggregate Functions

Here are the examples from class today. All use the MagazineSubscription database.

/******************************
This document provides examples
of aggregate functions, that is
fuctions that operate on more than
one row at a time
*******************************/

use MagazineSubscription

--the basic functions

SELECT Count(*) "Total Subscriptions"
from Subscription

Select Max(SubscriptionPrice) as "Most"
From MagazineDetail

Select Min(SubscriptionPrice) as Least
From MagazineDetail

--not a particularly meaninful number
Select Sum(SubscriptionPrice) as "so?"
From MagazineDetail

--more meaningful
Select AVG(SubscriptionPrice) as "Average Price"
From MagazineDetail

--With distinct (eliminates duplicates before
--computing average
Select AVG(Distinct SubscriptionPrice) as "Average Price"
From MagazineDetail


--more meaningful yet
Select SubscriptTypeID, AvG(SubscriptionPrice) "Average Price"
From MagazineDetail
Group by SubscripttypeID
Order by "Average Price" DESC

--using the having clause

Select SubscriptTypeID, AvG(SubscriptionPrice) "Average Price"
From MagazineDetail
Group by SubscripttypeID
having Avg(SubscriptionPrice) > 50
Order by "Average Price" DESC


--Count how many subscriptions each customer who has more than 2 has
Select CustID, Count(SubscriptionID)"Total Subscriptions"
From Subscription
Group by CustID
having Count(SubscriptionID) > 2



Further note: The last assignment in Assignment3 on Aggregate functions is not doable without subqueries which I have not shown you yet. Don't worry about it. I will show you how to do it Wednesday. (Monday is a Holiday)