Correlated subquery to find total larger than state average

Question

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 > .....?

Any ideas, please?

Answer 1

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.

Answer 2

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.