jeudi 5 novembre 2015

Averaging queries from multiple tables

I have three tables defined as follows:

merchant (name: string, price: integer) 
finance (no: string, gname: string, minCredit: integer) 
borrower (dname: string, gno: string, deliverydate: date)

example data set:

merchant |  finance |
ally s      jumbo
jim b       jumbo
jimmy g     small

borrower.dname and borrower.gno are foreign keys referencing merchant.name and finance.no

I am trying to write a query to find finances which have a strictly great number of borrowers than the average number of borrowers over all finance types. I am trying to output the finance number (no.)

What I have tried:

 select f.no
 from borrower a, borrower aa, finance f, merchant m
 where  count(a.no) > avg(aa.no)

This does not seem to give me the correct result. How do I do this query? Any help to put me on the right path?

Aucun commentaire:

Enregistrer un commentaire