-1

I am trying to query how many songs on average are on albums with the word "Rock" in the title. The chosen albums must have at least eight songs on them.

ER diagram

SELECT AVG(tr.track_id)
FROM (SELECT al.album_id AS album,
          tr.name,
          COUNT(tr.track_id)
    FROM track as tr
    LEFT OUTER JOIN album as al ON al.album_id = tr.album_id
    WHERE tr.name LIKE '%Rock%'
    GROUP tr.name
    ) AS ag
HAVING COUNT(al.album_id) >= 8;
philipxy
  • 14,867
  • 6
  • 39
  • 83
Maor Ohayon
  • 1
  • 2
  • [Why should I not upload images of code/data/errors when asking a question?](https://meta.stackoverflow.com/q/285551/3404097) An ERD is an image of DDL. – philipxy Mar 10 '23 at 05:17
  • There is no question in this post. PS What does the code have to do with the question? PS Debug questions require a [mre]. [ask] [Help] – philipxy Mar 10 '23 at 05:18

2 Answers2

1

You were close. The HAVING clause should be inside the inner query, filtering only those with more than 8 songs in it. Also you filtering by track name instead of album name, so I changed it as well.

SELECT AVG(ag.cnt)
FROM(SELECT al.album_id AS id,
            COUNT(*) as cnt
     FROM track as tr
     JOIN album as al ON al.album_id = tr.album_id
     WHERE al.name LIKE '%Rock%'
     GROUP al.album_id
     HAVING COUNT(al.album_id) >= 8 ) AS ag
sagi
  • 40,026
  • 6
  • 59
  • 84
  • sorry about it, too much hours trying to solve this.. so in the GROUP BY & WHERE i should place 'title' , yet it throw an error. on COUNT - i need the number of traks which supposed to be >= 8 – Maor Ohayon Aug 03 '22 at 18:02
0
SELECT AVG(ag.count_track)
FROM (
  SELECT 
     a.title,
     COUNT(t.track_id) as count_track
  FROM track AS t
  LEFT OUTER JOIN album AS a ON a.album_id = t.album_id
  WHERE a.title LIKE '%Rock%'
  GROUP BY a.title
) AS ag
WHERE count_track >= 8
Moritz Ringler
  • 9,772
  • 9
  • 21
  • 34
mailz
  • 1
  • Code-only answers are poor. Why/how does this answer the question? What question do you even think it is answering? How does this add anything to the answer already posted? Posts that add nothing should not be posted. [answer] [ask] [Help] Please indent code reasonably. – philipxy Mar 10 '23 at 05:19