SubQueries

--Sub queries
--where, select clause, from clause
use Community_Assist

--gives you the max per person key which is not what you
--really want to see
Select Personkey, max(donationAmount) from Donation
Group by PersonKey

--subqueries in the where clause
--allows you to see which donations are bigger than the average
--donation. The subquery must return only a single value,
--though more than one row might result in query results
Select DonationDate, Personkey, DonationAmount 
from Donation
where DonationAmount>(Select avg(donationAmount) from Donation)

--the "in" keyword lets you match against a set of results
--this returns all the people whose keys are in the Employee table
--ie employees. It is important to match like with like--personkey
--with personkey
Select personkey, PersonLastName, PersonFirstname, PersonEmail
From Person
Where personKey in (Select Personkey from Employee)

--with literal set
Select personkey, PersonLastName, PersonFirstname, PersonEmail
From Person
Where personKey in (2,4,5,129)

--subqueries with in can also be used like an outer join
--to show which services were NOT requested
Select GrantTypeKey, GrantTypeName from GrantType 
where GrantTypeKey not in (Select distinct GrantTypeKey from GrantRequest)

--In can be used to chain together subqueries. This one shows
--which employees did grant reivews. You can, of course,
--get the same information with a join
Select PersonKey, PersonLastName, PersonFirstName, PersonEmail
from person
Where PersonKey in
      (Select Personkey from Employee 
     where EmployeeKey in
  (Select EmployeeKey from GrantReview))

--which employees did NOT do reviews
Select PersonKey, PersonLastName, PersonFirstName, PersonEmail
from person
Where PersonKey in
      (Select Personkey from Employee 
     where EmployeeKey not in
  (Select EmployeeKey from GrantReview))

--subqueries in select
Select * from GrantRequest

--this queries uses subqueries in the SELECT clause. 
--Year, month, sum(GrantRequestAmount) and GrantTypeName are part
--of the normal query. The sum is grouped by
--year and month and grantTypeName
--The grand total requires a subquery, because you want the total
--of the whole table, not the sub group totals.
--the same is true of the percent. You have to divide
--the grouped by total by the Grand Total, which again
--must be done with a subquery.
Select Year(GrantRequestDate) [Year],
month(GrantRequestDate)[month],
GrantTypeName, 
sum(GrantRequestAmount) as Total,
(Select Sum(GrantRequestAmount) from GrantRequest) as GrandTotal,
sum(grantRequestAmount) / 
(Select Sum(GrantRequestAmount) from GrantRequest) * 100 as [Percent]
From GrantRequest
inner join GrantType
on GrantType.GrantTypeKey=GrantRequest.GrantTypeKey
Group by Year(GrantRequestDate), month(GrantRequestDate), GrantTypeName

--this is the same query but with formatting added
Select Year(GrantRequestDate) [Year],
month(GrantRequestDate)[month],
GrantTypeName, 
format(sum(GrantRequestAmount),'$#,##0.00') as Total,
format((Select Sum(GrantRequestAmount) from GrantRequest),'$#,##0.00') as GrandTotal,
format(sum(grantRequestAmount) / 
(Select Sum(GrantRequestAmount) from GrantRequest),'0.00 %') as [Percent]
From GrantRequest
inner join GrantType
on GrantType.GrantTypeKey=GrantRequest.GrantTypeKey
Group by Year(GrantRequestDate), month(GrantRequestDate), GrantTypeName

--Coordinated subqueries are queries in which the subquery 
--references the outer query. The subquery matches
--the granttypekey from gr1, the outer query with the granttypeKey
--from gr2 inside the subquery. The effect is to make sure
--that like is matched with like. GrantTypeKey 1 (Food) is matched 
--only against other granttypeKey 1 values, GrantTypeKey 2 (Rent)
--is matched only against other grantTypeKey 2 values, etc.
--a coordinated subquery is the SQL equivalent of a recursive
--function in other programming languages, and like them  
-- requires a lot of processor time 

Select GrantTypeKey, GrantRequestAmount
From GrantRequest gr1
Where GrantRequestAmount > 
(Select avg(GrantRequestAmount)
from GrantRequest gr2
Where gr1.GrantTypeKey=gr2.GrantTypeKey)

Select GrantTypeKey, GrantRequestAmount
From GrantRequest 
Where GrantRequestAmount > 
(Select avg(GrantRequestAmount)
from GrantRequest )

Select avg(GrantRequestAmount) From GrantRequest where GrantTypekey=3



Select * from Person
Select * from Employee