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
Thursday, January 21, 2016
Subqueries
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment