Aggregate Functions

--aggregate functions
--sum, max, avg, min, count 

use CommunityAssist 

--this and the next return all rows
Select COUNT(DonationKey) [Number of Donations] 
From Donation


Select COUNT(*) [Number of Donations] 
From Donation

--this only returns the number of those with dependents
Select COUNT(Dependents) from Employee
Select COUNT (*) From Employee

Select SUM(DonationAmount) total From Donation
--Distinct only includes unduplicated  values 
Select SUM(Distinct DonationAmount) total From Donation

Select AVG(DonationAmount) average From Donation
Select AVG(Distinct DonationAmount) average From Donation

Select DonationAmount from Donation order by DonationAmount desc

--return biggest (also with cast to format it)
Select '$' + Cast(MAX(donationAmount) as CHAR(10)) From Donation

--return smallest value
Select MIN(donationAmount)From Donation

--
Select * from Donation

--any column in the SELECT that is not a part of an aggregate function,
--must be included in a GROUP BY Clause
Select EmployeeKey, COUNT(DonationKey) [number of Donations]
From Donation
Group by EmployeeKey

--a scalar (row by row) function with an aggregate
--you have to group by the scalar
Select MONTH(DonationDate) [Month], SUM(DonationAmount) total
From Donation
Group by MONTH(DonationDate)
Order by [Month]

--You must use HAVING with any criteria that uses an
--aggregate function
--these examples have both a having and a where clause
Select EmployeeKey, AVG(DonationAmount) Average
From Donation
Where EmployeeKey > 2
Group by EmployeeKey
Having AVG(DonationAmount) < 1000

Select Lastname,COUNT(Lastname) From Person
Where LastName Like 's%'
Group by lastname
Having count(LastName) > 1