Agrupar y contar registros

[engonga]

hola estoy intentando hacer una consulta cruzada con dos tablas

Código HTML:

SELECT  key1.id, key1.user_nicename, key1.user_email, key1.display_name
FROM wp_users key1 INNER JOIN wp_posts key2 ON key1.id = key2.post_author
WHERE key2.post_type='reviews' 
AND key2.post_status='publish'

esta consulta me saca una tabla así



2 admin
admin

2 admin
admin

10 andreyit3_1rbe
Manuel

3 luz-arambarri
luz arambarri

14 joan
joan

2 admin
admin

4 andreyit3
andy

13 twi_markfischer12
matias ledesma

entonces quiero que por ejemplo el usuario admin que tiene dos registros

me saque una table de este formato

registros usuario
2 admin
1 joan


he intantado hacerlo con un count (*)

Código HTML:

SELECT count(*) as qty,key1.id, key1.user_nicename, key1.user_email, key1.display_name
FROM wp_users key1 INNER JOIN wp_posts key2 ON key1.id = key2.post_author
WHERE key2.post_type='reviews' 
AND key2.post_status='publish'
ORDER BY qty DESC

pero solo me saca una linea asi

qty usuario
8 admin

no se si me he explicado bien

[jurena]

SELECT t1.qty, wpu.id, wpu.user_nicename, wpu.user_email, wpu.display_name FROM wp_users wpu INNER JOIN (SELECT count(*) as qty, key1.id
FROM wp_users key1 INNER JOIN wp_posts key2 ON key1.id = key2.post_author
WHERE key2.post_type='reviews'
AND key2.post_status='publish' GROUP BY key1.id)t1 ON t1.key1.id = wpu.id
ORDER BY t1.qty DESC

No lo he probado, también se podría sin subconsulta

[engonga]

voy a probarlo y te digo algo

muchas gracias

[engonga]

no funciona lo estoy probando directamente en el phpmyadmin y me lanza el error

#1054 - Unknown column 't1.key1.id' in 'on clause'

[jurena]

SELECT t1.qty, wpu.id, wpu.user_nicename, wpu.user_email, wpu.display_name FROM wp_users wpu INNER JOIN (SELECT count(*) as qty, key1.id
FROM wp_users key1 INNER JOIN wp_posts key2 ON key1.id = key2.post_author
WHERE key2.post_type='reviews'
AND key2.post_status='publish' GROUP BY key1.id)t1 ON t1.id = wpu.id
ORDER BY t1.qty DESC

[engonga]

gracias ya funciona