A Query using a Correlated Subquery

SELECT vendor_id, invoice_number, invoice_total

FROM invoices i

WHERE invoice_total >

(SELECT AVG(invoice_total)

FROM invoices

WHERE vendor_id = i.vendor_id)

ORDER BY vendor_id, invoice_total

In This Query I want to know the average invoice amount for each Vendor (Who are my most important vendors, the ones I use the most)

The Sub Query is averaging each Vendors invoices

The Parent Query is displaying all the Averages for every Vendor with more than one invoice

Notice the subquery is tied to the parent query

Note