Get blog archive format of dates by count and grouped by month / year

archive.sql

SELECT 
  news_categories.name AS category,
  YEAR(news_articles.creation_date) AS year,
  MONTH(news_articles.creation_date) AS month,
  MONTHNAME(news_articles.creation_date) AS month_name,
  COUNT(*) AS total
FROM news_articles 
INNER JOIN news_categories ON news_articles.category_id=news_categories.id
WHERE DATE(news_articles.creation_date) >= DATE_SUB(NOW(), INTERVAL 2 YEAR)
GROUP BY category, year, month
ORDER BY year, month, category;