How to count occurrences with derived tables in SQL?-CodePudding

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

CodePudding user response：

You can try the following:

WITH stats as
(
SELECT Name
      ,COUNT(id) as count_ids
FROM MyTable
GROUP BY Name
)

SELECT Name
      ,count_ids
FROM
(
SELECT Name
      ,count_ids
      ,RANK() OVER(ORDER BY count_ids DESC) as rank_ -- this ranks all names
FROM stats
) s
WHERE rank_ = 1 -- the most popular ```

This should work in TSQL.

CodePudding user response：

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