bueno estaba retocando un proyecto que tengo y tenia que hacer lo mismo que vos, asi que me puse a ver como cornos lo hace wordpress, el wp tiene una funcion que se llama wp_get_archives() asi que me puse a analizarla para entender la logica y si bien es una chorrera de codigo no es complicado entenderla, basicamente esa funcion permite mostrar el archivo por meses, años, dias o semanas. En mi caso lo hice por año y mes. En consecuencia mi query queda de la siguiente forma:
Código PHP:
SELECT DISTINCT YEAR( time ) AS anio ,
CASE MONTH( time )
WHEN 1
THEN 'Enero'
WHEN 2
THEN 'Febero'
WHEN 3
THEN 'Marzo'
WHEN 4
THEN 'Abril'
WHEN 5
THEN 'Mayo'
WHEN 6
THEN 'Junio'
WHEN 7
THEN 'Julio'
WHEN 8
THEN 'Agosto'
WHEN 9
THEN 'Septiembre'
WHEN 10
THEN 'Octubre'
WHEN 11
THEN 'Noviembre'
WHEN 12
THEN 'Diciembre'
END AS mes,
Month(time) as id_mes,
count( time ) AS posts
FROM nuke_stories
WHERE topic =9
GROUP BY YEAR( time ) , MONTH( time )
ORDER BY time ASC
solo es cuestion que lo adaptes a los campos y tablas de tu base de datos...
salu2