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?


You can do it like this:

select * 
    dbo.Invoice I1 
    join dbo.Vendors V1 on V1.VendorID = I1.VendorID
    I1.InvoiceTotal > (
            dbo.Invoices I2 
            join dbo.Vendors V2 on V2.VendorID = I2.VendorID
            V1.VendorState = V2.VendorState 

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

from (
        Avg(I.InvoiceTotal) over (partition by V.VendorState) as AvgInv
        dbo.Invoices I
        join dbo.Vendors V on V.VendorID = I.VendorID
) X
    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.

| improve this answer | |

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.