SQL- query which finds the name of a specific row which has max linked columns from another table

There are 2 tables:

   users: id, name
   albums: id, user_id

I need a SQL query which finds the NAME of the user who has the max count of albums. How do I do that?

Here is what I have tried so far –

SELECT Users.name 
FROM Users 
JOIN Albums
ON Users.id = Albums.user_id 
HAVING  COUNT(Albums.id) = MAX(COUNT(Albums.id)); 

(this doesnt work)

Answer

This sub query should return the name of the user having maximum number of albums.

SELECT name FROM Users WHERE id = (SELECT user_id FROM Albums GROUP BY user_id ORDER BY COUNT(*) DESC LIMIT 1);

Here is the GROUP BY used to group the user_id with the COUNT of each user_id occurrences otherwise return the count for all records in the table.

If there multiple max occurrences with same count then you have to use IN keyword for the query to return all names and the query will be bit complex.