Sql getting data where acquisitionprice > askingprice

See more:

SQL

MySQL

Trans

(TransID, AcquisitionPrice, AskingPrice, WorkID)

Work

(WorkID, Title, Artist ID)

Artist

(ArtistID, FirstName, LastName)

Question is to list the work ID, title and artist name of all the works of art that sold for more than the average sales price, and the price they sold for.

Hence output should be:

WorkID Title FirstName LastName AcquisitionPrice



I have problem getting this output and calculating the average price. MySQL always says there is something missing.

What I have tried:

SELECT T.WorkID, w.Title, A.LastName, A.FirstName
FROM trans T
JOIN Work w ON T.WorkID = w.WorkID
JOIN Artist A
ON T.acquisitionprice > T.ASKINGPRICE;
(I'm sure the last part is wrong)