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 &gt; .....?


Any ideas, please?
You can do it like this:

select * 
from 
    dbo.Invoice I1 
    join dbo.Vendors V1 on V1.VendorID = I1.VendorID
where 
    I1.InvoiceTotal &gt; (
        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 &gt; 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.