10 Find the average invoicetotal for each vendor in CA Here

10. Find the average invoice_total for each vendor in CA.

Here i use Invoices, Vendors tables and vendor_state and vendor_id colum.

i looking join or subquery both answer.

my solution is here but not working

Select avg(Invoice_totla)

from Invoices i

Where i. vendor_id = selecet (vendor_state from vendors

where vendor_state Like \'CA\');

Solution

subquery

Select avg(Invoice_total) from Invoices i where i. vendor_id IN ( select vendor_id from vendors where vendor_state = \'CA\');

The inner query results the list of all vendors in the state CA.

The outer query computes average of Invoice_total where vendor_id is in the list results from inner query ie those vendors who live in CA

join

Select avg(i.invoice_total) from Invoices i inner join Vendors v on i.vendor_id = v.vendor_id where v.vendor_state = \'CA\';

The query joins two tables on vendor_id where vendor lives in CA ,it calculates the average of invoice_total