Aggregate Functions

Use CommunityAssist

--problem from homework 2
Select employeeKey, Coalesce(cast (dependents As varchar), 'none') as Dependents
From Employee

--if we had replaced with a number instead of a word
Select employeeKey, Coalesce(dependents, 0) as Dependents
From Employee


Select * From Employee

--Aggregate Functions Sum Count Avg Min Max

Use MagazineSubscription

--counts all rows
Select COUNT(*) From customer

--doesn't count nulls
Select COUNT(CustPhone) From Customer
--embedded coalesce
Select COUNT (Coalesce (CustPhone, 'none')) From Customer

Select * From Subscription

Select COUNT(custID) from Subscription

--only get distinct values
Select Count(Distinct Custid) From Subscription

Select SUM (subscriptionPrice) as [Total Prices] From MagazineDetail
Select SUM (Distinct subscriptionPrice) as [Total Prices] From MagazineDetail

Select AVG(SubscriptionPrice) as Average from MagazineDetail

Select AVG(Distinct SubscriptionPrice) as Average From MagazineDetail

Select MAX(SubscriptionPrice) As Biggest from MagazineDetail
Select Min(SubscriptionPrice) As Biggest from MagazineDetail

Select * from Customer

--you cannot have any column in a select clause that contains an aggregate function,
--that is not a part of an aggregate function, unless it is included in a Group By Clause
Select SubscriptTypeID, AVG(SubscriptionPrice) as Average
From MagazineDetail
Where SubscriptTypeID > 3 --normal criteria based on table values
Group By SubscriptTypeID
Having AVG(SubscriptionPrice) > 70 --when you have an aggregate function in the criteria

--It all has to be in the above order
--Select
--From
--Where [optional]
--Group by
--having [optional]

--one way to see the max and the magazine detail which has the maximum price together
--involves subqueries which are in assignment 5
Select MagDetID ,(Select MAX(SubscriptionPrice) from MagazineDetail) from MagazineDetail
Where SubscriptionPrice = (Select MAX(SubscriptionPrice) from MagazineDetail)

--a way to find out which magdetID has the maximum price
--once we know the maximum price
--Having requires a group by even if no aggregate function
--in the select clause
Select MagDetID From MagazineDetail
Group by MagDetID
Having MAX(SubscriptionPrice)=200.50

--problem from homework 3
Use CommunityAssist

Select MONTH(GrantDate) as [Month], SUM (GrantAmount) as Total
From ServiceGrant
Group By MONTH(GrantDate)