Correlated subquery to find total larger than state average

I have tables Vendors (VendorName, VendorState,....) and Invoices (InvoiceID, InvoiceTotal,...). I want to get the Invoices (as InvoiceId) that are larger than the Avg state InvoiceTotal.

I know I first find the avg total per state:

``````SELECT  VendorState, Avg(InvoiceTotal) AS AvgStateInvoice
from Invoices I join Vendors V on V.VendorID= I.VendorID
group by VendorState
``````

So I now have the list of avg InvoiceTotal by state. Now I need to figure out:

How to do an outer query to select those invoices larger than the state average and here is where I am lost, since I don't remember the syntax to do the comparison . I guess it would be something of the sort:

``````SELECT InvoiceId from Invoices where InvoiceTotal > .....?
``````

You can do it like this:

``````select *
from
dbo.Invoice I1
join dbo.Vendors V1 on V1.VendorID = I1.VendorID
where
I1.InvoiceTotal > (
SELECT
Avg(I2.InvoiceTotal)
from
dbo.Invoices I2
join dbo.Vendors V2 on V2.VendorID = I2.VendorID
where
V1.VendorState = V2.VendorState
);
``````

Or using window functions, which may be faster since it doesn't need an extra join:

``````SELECT X.*
from (
select
*,
Avg(I.InvoiceTotal) over (partition by V.VendorState) as AvgInv
from
dbo.Invoices I
join dbo.Vendors V on V.VendorID = I.VendorID
) X
where
X.InvoiceTotal > X.AvgInv;
``````

The window function option might not be faster. Though it saves a join in the query, the execution plan will feature a subexpression spool (with two extra joins). The table spool is needed to compute and save the average for the current partition. The spooled result is replayed once per partition.

This one worked for me, I add it because I include plenty of detail below I hope will help others who have trouble understand better than by just having a solution (And I did not ask for an explanation when I posed the question) :

``````SELECT VendorState, InvoiceDate FROM Invoices I JOIN Vendors V ON
V.VendorID=I.VendorID WHERE InvoiceDate IN (Select Min(InvoiceDate) FROM
Invoices JOIN Vendors ON Invoices.VendorID=Vendors.VendorID
WHERE  V.VendorState=Vendors.VendorState)
``````

It worked notwithstanding the fact that I can't see the formatting section.

The outside sets up the needed entries VendorState and InvoiceDate and the needed tables : Vendors, Invoices and the join needed to have them both then the inner table/parentheses restricts the values ; the first part in the inner table provides the first table needed for the correlation on VendorState. Ultimately, the expression V.VendorState=Vendors.VendorState AKA the correlation, is done so that there is a comparison of MinInvoiceState over all Vendor States, i.e., over all InvoiceDate entries in the field. VendorState. Note that two copies of the table Vendors (containing a list of VendorState ) are necessary to do the correlation.