Search code examples
sqldatabaseoracle-databasesubquerycorrelated-subquery

Correlated Subquery, oracle sql

I am working on my homework and I couldn't figure it out this problem. Can anyone help me please? This Oracle Sql

Display each invoice amount that is higher than the vendor’s average invoice amount. You must use a correlated subquery.

I tried but I am not that close. Here my code is:

    SELECT vendor_id, invoice_number, invoice_total
FROM ap.invoices
WHERE invoice_total  >  (SELECT AVG(invoice_total) 
                     FROM ap.invoices
                     where invoice_id=invoice_id
                     )
ORDER BY vendor_id;

Result table should match this image : https://i.sstatic.net/w9D6i.png

Solution

  • Use an alias for the outer query table, and correlate on the vendor_id field:

    SELECT vendor_id, invoice_number, invoice_total
FROM ap.invoices i
WHERE invoice_total  >  (
    SELECT AVG(invoice_total) 
    FROM ap.invoices v
    where v.vendor_id = i.vendor_id
)
ORDER BY vendor_id;