#1111 -组函数的无效使用: MAX COUNT不适用于MYSQL5.5

Question

我有一个表userInteret，我想在用户之间的公共互连上选择最大值。我尝试了这个查询：

SELECT nomInteretUser,idUser,COUNT(nomInteretUser) FROM `userInteret` 
WHERE nomInteretUser IN('piano','flute','chien') GROUP BY idUser

但我想要这个数的最大值，当我尝试这个：

SELECT MAX(COUNT(nomInteretUser))FROM userInteret 
WHERE nomInteretUser IN('piano','flute','chien') GROUP BY idUser;

它总是犯错误：

MySQL a简历:文档 1111 -无效使用组函数

Answer 1

SELECT idUser,COUNT(nomInteretUser) counts 
FROM userInteret
WHERE nomInteretUser IN('piano','flute','chien') 
GROUP BY idUser
ORDER BY counts DESC LIMIT 1

Answer 2

SELECT MAX(num) FROM (
    SELECT nomInteretUser,idUser,COUNT(nomInteretUser) as num FROM userInteret WHERE nomInteretUser IN('piano','flute','chien') GROUP BY idUser
) AS MYalias