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?