Aggregate Functions

--Aggregate functions: count, sum, avg, min, max
use Automart;
--counts all rows
Select count(*) as [number] from Person

Select Count(*) [Number of Employees]From Employee
Where LocationID=1

--aggregate functions ignore nulls
Select count(SupervisorID) [Supervisors] from Employee
Where LocationID=1

Select * from Employee

--
Select Sum(ServicePRice) from Customer.Autoservice
Select avg(ServicePrice) from customer.Autoservice
Select min(ServicePrice) from Customer.Autoservice
Select ServiceName, Max(ServicePrice) from Customer.AutoService
Group by ServiceName

--for future reference, how to get which row matches
--a min or max value. Need to use a subquery
Select ServiceName, ServicePrice
from customer.autoservice
Where ServicePrice = (Select max(servicePrice) From customer.AutoService)

--you must group by any column that is not
--a part of the aggregate function
--in this case, locationID
Select LocationID, count(VehicleServiceID) [total services]
From Employee.VehicleService
Group by LocationID

--the where clause can still be used if the criteria is a 
--simple 'scalar', row by row value. Having is used 
--when the criteria is an aggregate function 
--the where must always come before the group by
--the having always comes after
Select LocationID, month(ServiceDate) [Month], count(VehicleServiceID)[total Services]
From Employee.VehicleService
where LocationID=2
Group by LocationID, month(ServiceDate)
having count(vehicleServiceID) > 5
order by locationID