Get blog archive by year

archive_by_year.sql

SELECT 
  categories.name,
  MONTHNAME( articles.created_at ) AS month_name,
  MONTH( articles.created_at ) AS month,  
  YEAR( articles.created_at ) AS year, 
  COUNT( articles.id ) as news_count 
FROM  news_categories categories, news_articles articles
WHERE
  articles.published = 1
  AND categories.id = articles.category_id
  AND YEAR(articles.created_at) = 2013
GROUP BY  categories.name, month, year 
ORDER BY year DESC, month DESC, name;