Aggregate Functions

--aggregate functions

Use communityAssist

--basic aggregate functions
--aggregate functions operatate on multiple rows
Select Sum(DonationAmount) From donation
Select Avg(DonationAmount) as average From Donation
--counts all rows
Select count(*) [Number of Rows] From Donation
--doesn't count nulls
Select Count(donationAmount) from Donation
--all rows
Select Count(*) from PersonAddress
--minus nulls
Select Count (Apartment) From PersonAddress
Select * from PersonAddress

Select Min(DonationAmount) as smallest from Donation
Select Max(DonationAmount) as smallest from Donation

--Doesn't include duplicate values
Select sum(distinct DonationAmount) From Donation
Select avg(Distinct DonationAmount)  from Donation
--get unique donors
Select count(Distinct PersonKey) from Donation

--group by. You must group by any column in the select
--that is not a part of an aggregate function
--(if there are aggregate functions in the select)
Select City, Count(city) [count] From PersonAddress
group by City

--a query with group by that shows the totals
--earned for each month of the year
Select Year(donationDate) as [Year], Month(donationDate) as [Month],
Sum(donationAmount) as total
From Donation
Group by Year(donationDate), Month(donationDate)
Order by Year(DonationDate)

Select Year(donationDate) as [Year], Month(donationDate) as [Month],
Sum(donationAmount) as total
From Donation
Where Month(donationDate) > 2
Group by Year(donationDate), Month(donationDate)
Having sum(donationAmount) > 6000
Order by Year(DonationDate)