How to count occurrences with derived tables in SQL?

I have this very simple table:

CREATE TABLE MyTable 
( 
    Id INT(6) PRIMARY KEY,
    Name VARCHAR(200) /* NOT UNIQUE */
);

If I want the Name(s) that is(are) the most frequent and the corresponding count(s), I can neither do this

SELECT Name, total
FROM table2
WHERE total = (SELECT MAX(total) FROM (SELECT Name, COUNT(*) AS total
                                       FROM MyTable GROUP BY Name) table2);

nor this

SELECT Name, total
FROM (SELECT Name, COUNT(*) AS total FROM MyTable GROUP BY Name) table1
WHERE total = (SELECT MAX(total) FROM table1);

Also, (let's say the maximum count is 4) in the second proposition, if I replace the third line by

WHERE total = 4;

it works.
Why is that so?

Thanks a lot

Solution

Your queries can't be executed because "total" is no column in your table. It's not sufficient to have it within a sub query, you also have to make sure the sub query will be executed, produces the desired result and then you can use this.

You should also consider to use a window function like proposed in Dimi's answer. The advantage of such a function is that it can be much easier to read. But you need to be careful since such functions often differ depending on the DB type.

If you want to go your way with a sub query, you can do something like this:

SELECT name, COUNT(name) AS total FROM myTable
GROUP BY name
HAVING COUNT(name) =
(SELECT MAX(sub.total) AS highestCount FROM
(SELECT Name, COUNT(*) AS total
FROM MyTable GROUP BY Name) sub);

I created a fiddle example which shows both queries mentioned here will produce the same and correct result: db<>fiddle