Aggregate functions

--problems 8 and 9 from assignment 2
Select GrantKey, GrantAmount, PersonKey,
Case ServiceKey
When 1 then 'food'
when 2 then 'Rent'
when 3 then 'Child care'
when 4 then 'transportation'
when 5 then 'medical'
else 'other'
End
As "Service Name"
From ServiceGrant

Select EmployeeKey, HireDate,
coalesce(cast(Dependents AS Varchar), 'none') as Dependents
From Employee


Select EmployeeKey, Hiredate, ISNULL(convert ( varchar, dependents),'None') from employee

--aggregate functions
Use MagazineSubscription

--count all rows
Select COUNT(*) as total from Magazine

Select COUNT(*) from customer

--this is a way to include nulls in the count by
--converting the null to another value using coalesce
Select COUNT(Coalesce (CustPhone, 'none')) as phones from Customer

--this returns a count of all the customer ids in subscription
Select COUNT(CustID) as customers from Subscription
--this returns a count of only unique customer ids from subscription
Select COUNT(Distinct CustID) as customers from Subscription
--totals the prices, not too meaningfull
Select SUM(SubscriptionPrice) as totalPrice from MagazineDetail
-- returns the avg subscription price
Select AVG(SubscriptionPrice) as averagePrice
from MagazineDetail

Select MAX(SubscriptionPrice) as Highest
From MagazineDetail

Select MIN(SubscriptionPrice) as Highest
From MagazineDetail

--any column not a part of the aggregate function--in this case
--subscriptTypeId--must be included in a group by clause
Select SubscriptTypeID, AVG(SubscriptionPrice) as average
From MagazineDetail
Group by SubscriptTypeID

--this brings together several things
--for one it mixes scalar (row by row) functions and an aggregate
--function "count.")
--the scalar functions must be included in the group by clause just
--like regular columns not included in the aggregate function
--there is also a where clause which must come before the group by.
--it handles non-aggregate criteria
--the having clause handles aggregate criteria
--it must follow the group by clause
--the order by comes last
--the order by is one of the few places you can get away
--with using the alias of a column
Select Year(SubscriptionStart) as [Year],MONTH(subscriptionStart) as [Month], COUNT(SubscriptionID) as Number
from Subscription
Where MONTH(SubscriptionStart) between 2 and 4
Group by Year(SubscriptionStart), MONTH(SubscriptionStart)
Having COUNT(SubscriptionStart) > 5
Order by Number Desc

--to get the magazine id that has the minimum value you
--must use a subquery
Select MagID, SubscriptionPrice
From MagazineDetail
Where SubscriptionPrice=
(Select MIN(SubscriptionPrice)
From MagazineDetail)