You need to find which artist (artname) has the maximum average price (average cdprice). There are tables

Artist: artist

CD: cd

I tried to write:

SELECT artname FROM cd, artist GROUP BY artname HAVING (SUM(cdPrice)/COUNT(cd.artid)) >= ALL(SELECT (SUM(cdprice)/COUNT(cd.artid)) FROM cd,artist ar WHERE ar.artid=cd.artid GROUP BY ar.artname);

But it did not work out.

  • You need to start with the condition of the relationship between the tables, i.e. you have a cartesian product. - msi

3 answers 3

The most versatile option:

 select a.artname from (select cd.artid, avg(cd.cdprice) as avg_cdprice from cd group by cd.artid ) c join artist a on c.artid = a.artid where c.avg_price = (select max(avg_price) from (select cd.artid, avg(cd.cdprice) as avg_cdprice from cd group by cd.artid ) cc )

    The easiest option:

    Find an artist with the maximum average price of a record

     SELECT TOP (1) artid FROM CD GROUP BY (artid) ORDER BY avg(cdprice) DESC

    Respectively find the name of the artist

     SELECT artname FROM Artist WHERE artid = ( SELECT artid FROM CD GROUP BY (artid) ORDER BY avg(cdprice) DESC LIMIT 1 )

    UPD . Corrected by PostgreSQL

    • There is no such function: ERROR: function top (integer) does not exist LINE 1: SELECT artname FROM artist WHERE artid = (SELECT TOP (1) artid ... ^ HINT: You might need to explicit explicit type casts. - Roar RaizZer
    • @RoarRaizZer agrees, limit 1 will replace it - Nick Proskuryakov
    • Thank you for your help - Roar RaizZer

    I could figure it out myself:

     SELECT artName FROM Artist, CD WHERE Artist.artID = CD.artID GROUP BY artName HAVING AVG(cdPrice) >= ALL(SELECT AVG(cdPrice) FROM CD GROUP BY cdGenre);