Subqueries

Use CommunityAssist
--subqueries

Select PersonLastName, GrantAllocation
From Person p
Inner join ServiceGrant sg
on p.PersonKey=sg.PersonKey
Where GrantAllocation=
    (Select Min(GrantAllocation) from serviceGrant)

Select PersonLastName, PersonFirstName
From Person where Personkey in
(Select PersonKey from Employee) 

Select PersonLastName, PersonFirstName
From Person where Personkey in
 (Select PersonKey from Employee
  where employeeKey in 
   (Select EmployeeKey from ServiceGrant))

--The total and count GrantTotal granted and the percent
--of the grants for each service

Select ServiceName, Sum(GrantAllocation) as Total, 
(Select max(GrantAllocation) from ServiceGrant) as Biggest,
(Select Sum(GrantAllocation) from ServiceGrant) GrandTotal
From ServiceGrant sg
Inner join CommunityService cs
on sg.ServiceKey=cs.ServiceKey
Group by ServiceName

Select ServiceName,count(GrantAllocation) as number, Sum(GrantAllocation) as Total, 
(Select max(GrantAllocation) from ServiceGrant) as Biggest,
(Select Sum(GrantAllocation) from ServiceGrant) GrandTotal,
format(Sum(GrantAllocation) / (Select Sum(GrantAllocation) from ServiceGrant), '###.##%') [Percent]
From ServiceGrant sg
Inner join CommunityService cs
on sg.ServiceKey=cs.ServiceKey
Group by ServiceName

Select ServiceName, Max(GrantAllocation) as largest
From CommunityService cs
Inner Join ServiceGrant sg
on cs.ServiceKey =sg.ServiceKey
Group by ServiceName

 Select ServiceKey, avg(GrantAllocation)
 From ServiceGrant
 Group by ServiceKey

 Select ServiceKey, GrantAllocation
 From ServiceGrant sg1
 Where GrantAllocation >
 (Select Avg(GrantAllocation) 
 From ServiceGrant sg2
 Where sg1.ServiceKey=sg2.ServiceKey)

 Select Avg(GrantAllocation) from ServiceGrant
 Where ServiceKey =2