contar resultados repetidos

[gnzsoloyo]

Mira, ,volviendo al principio de todo, si lo que quieres es esto:

Felip tiene 2 validado, 1 esperando
edu tiene 1 esperando, 1 validado, 1 cancelado
manu tiene 3 cancelado

Pero tu query es esta:

Código MySQL:

SELECT autor,estado, COUNT(autor ) total
FROM top
WHERE estado LIKE '2'
GROUP BY autor 
ORDER BY total DESC
LIMIT 50
 

Eso jamás lo vas a obtener, porque estás excluyendo en ella a todos los estados 1, 3, y cualquier otro que haya (0, NULL). Ergo, eso te devolverá ceros donde haya otros valores.
Lo que más se parece a lo que quieres es lo que te propone @andresdzphp:

Código MySQL:

SELECT 
    autor,  
    SUM(IF(estado = 1, 1, 0)) esperando,  
    SUM(IF(estado = 2, 1, 0)) validado,  
    SUM(IF(estado = 3, 1, 0)) cancelado 
FROM top 
GROUP BY autor
 

En ese caso, tu objetas que "cancelado" te lo devuelve como cero, pero el error no está en la consulta, sino que tu insistes en poner ese LIKE '2', que no tiene ningún sentido para el resultado que pretendes, como ya te lo expliqué:

Código MySQL:

SELECT 
    autor,  
    COUNT(autor ) totpages, 
    SUM(IF(estado = 2, 1, 0)) validado,  
    SUM(IF(estado = 3, 1, 0)) cancelado
FROM top
WHERE estado LIKE '2'
GROUP BY autor 
ORDER BY totpages DESC
LIMIT 50
 

¿Por qué insistes con ese LIKE?
Con sólo suprimirlo, te dará lo que deseas.

Tip final: LIKE se usa exclusivamente con cadenas de texto, y tiene sentido utilizarlo si y sólo si vas a usar comodines ("%"), ya que de lo contrario tiene el mismo efecto que el "=".
Y en tu caso no tiene ningún sentido buscar por un aproximado, porque el valor es puntual.
El LIKE es un vicio de programadores, producto de espantosos manuales que lo incluyen como solución única, sin tomarse el trabajo de aclarar las desventajas que posee a la hora del uso de indices, performance y contexto de uso.
Si no vas a buscar valores aproximados, entonces no lo uses. Es preferible hacer esto:

Código MySQL:

SELECT 
    autor,  
    COUNT(autor ) totpages, 
    SUM(IF(estado = 2, 1, 0)) validado,  
    SUM(IF(estado = 3, 1, 0)) cancelado
FROM top
WHERE estado = 2 OR estado = 1 OR estado = 3
GROUP BY autor 
ORDER BY totpages DESC
LIMIT 50
 

O bien esto:

Código MySQL:

SELECT 
    autor,  
    COUNT(autor ) totpages, 
    SUM(IF(estado = 2, 1, 0)) validado,  
    SUM(IF(estado = 3, 1, 0)) cancelado
FROM top
WHERE estado IN(1, 2, 3)
GROUP BY autor 
ORDER BY totpages DESC
LIMIT 50
 

Y si vas a usar LIKE usa sólo uno de dos formas: con el comodín al principio o con el comodín al final:

Código MySQL:

SELECT a, b, c, d, e
FROM tabla
WHERE e LIKE '%a';
 

o bien:

Código MySQL:

SELECT a, b, c, d, e
FROM tabla
WHERE e LIKE 'a%';
 

Poner en ambos lados el comodín destruye la performance de la consulta porque hace un full table scan, y además no es razonable por cuestiones de uso de interfaz de los usuarios (la razón es explicación larga, pero la idea es que casi nadie busca algo por el final, sino por el principio del texto, porque es lo que mejor recuerda, y para los otros casos es mejor usar indices FULLTEXT con MATCH() AGAINST(), y no LIKE).

Al usar LIKE con el comodín a un sólo lado, a MySQL le resulta más sencillo usar los índices porque puede buscar el patrón inicial o final más rápidamente, ya que sí aparecerán en los índices creados sobre esos campos, mientras que con ambos comodines no lo puede determinar sin leer el índice entero... y en ese caso descarta el índice y lee toda la tabla.
¿Se entiende la lógica de búsqueda que te digo?