Aggregate Functions

Here is the in-class code for Aggregate Functions

Use CommunityAssist 

--aggregate function

Select COUNT(PersonKey) "Number of Donations"
From Donation

--DISTINCT will return only unique values -- in the following
--it returns only unique persons for donors
Select COUNT(Distinct PersonKey) [Number of Donors]
From Donation

Select SUM(donationAmount) [total donations]
From Donation

Select Avg(donationAmount) "Average"
From Donation

Select MAX(donationAmount) "Highest"
From Donation

Select MiN(donationAmount) "Highest"
From Donation

--if you have an aggregate function in the SELECT
--any other column in the SELECT not a part of an 
--aggregate function must be contained in a
--GROUP BY clause
Select EmployeeKey, COUNT(DonationKey) "Count",
sum(DonationAmount) as Total
From Donation
Group by EmployeeKey

--this does several things:
--MONTH and YEAR are scalar functions
--that means they operate on one row at a time
--SUM is an aggreagate function operating on 
--multiple rows--so the MONTH and YEARfunctions
--must be contained in a group by clause
--the aliases don't work, you must do the functions
--themselves
--if you have an aggergate function in your 
--criteria you must use HAVING instead of WHERE
--you can use both in a query as in the example below
--the WHERE comes after the FROM
--the HAVING comes after the GROUP BY

Select MONTH(DonationDate) [Month], Year(DonationDate) [Year],SUM(donationAmount) total
From Donation
Where YEAR(DonationDate)=2010
Group by Year(donationDate), Month(DonationDate)
having SUM(donationAmount) > 3000

---

Here is a solution to the substring problem where it wouldn't return single worded entries. The problem was it was looking for a space and none existed. The solution, if inelegant is to concatenate in a space within the charIndex function so that every column has a trailing space.

Select SUBSTRING(VehicleMake, 1, CHARINDEX(' ', VehicleMake+' ', 1))
From Customer.Vehicle