subqueries

use Community_Assist

--subqueries
Select max(donationamount) from Donation

Select PersonLastName, DonationDate, DonationAmount
From donation
inner join Person
on Donation.PersonKey=Person.Personkey
Where DonationAmount=(Select max(DonationAmount) from Donation)

--Which employees Reviewed grants
Select PersonLastName, PersonFirstName, PersonEmail 
From Person
where personkey in 
   (Select personkey from Employee 
     where employeeKey in
  (Select EmployeeKey from GrantReview))

--Sub queries in the select clause
Select GrantTypeName, Sum(GrantRequestAmount) subtotal
From GrantRequest
inner join GrantType
on GrantType.GrantTypeKey=GrantRequest.GrantTypeKey
Group by GrantTypeName

--add a subquery to get the overall total
Select GrantTypeName, Sum(GrantRequestAmount) subtotal,
(Select Sum(GrantRequestAmount) from GrantRequest)Total
From GrantRequest
inner join GrantType
on GrantType.GrantTypeKey=GrantRequest.GrantTypeKey
Group by GrantTypeName

--add percent
Select GrantTypeName, Sum(GrantRequestAmount) subtotal,
(Select Sum(GrantRequestAmount) from GrantRequest)Total,
sum(GrantRequestAmount)/(Select Sum(GrantRequestAmount) from GrantRequest) * 100 [Percent]
From GrantRequest
inner join GrantType
on GrantType.GrantTypeKey=GrantRequest.GrantTypeKey
Group by GrantTypeName

--formatted
Select GrantTypeName, format(Sum(GrantRequestAmount), '$#,##0.00') subtotal,
count(*) Number,
format((Select Sum(GrantRequestAmount) from GrantRequest),'$#,##0.00') Total,
format(sum(GrantRequestAmount)/(Select Sum(GrantRequestAmount) from GrantRequest),'##0.00%') [Percent]
From GrantRequest
inner join GrantType
on GrantType.GrantTypeKey=GrantRequest.GrantTypeKey
Group by GrantTypeName

--Correlated sub query is a query
--where the inner query references the outer query
--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 avg(GrantRequestAmount) from GrantRequest 
Where GrantTypekey=3

Here is the insert into table with a SELECT

--using a select to populate a table

Use Community_Assist

Create table PersonA
(
   personkey int,
   personLastName nvarchar(255),
   personfirstname nvarchar(255),
   personemail nvarchar(255)
)
Insert into PersonA
Select PersonKey, PersonLastName,
Personfirstname, personemail
From person
where PersonlastName like 'A%'

Select * from PersonA