Correlated subquery to find total larger than state average
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.