MySQL archiving query
New here? Learn about Bountify and follow @bountify to get notified of new bounties! x

Want to pull records for 2 years and group by category, year, and month for blog archive.

Count is generating by month/year but not category/month/year.

Here's what I have:

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 category_id  
WHERE DATE(news_articles.creation_date) >= DATE_SUB(NOW(), INTERVAL 2 YEAR)  
GROUP BY category, year, month  
ORDER BY year, month; 

This will generate:

| category    | year | month     | total |
| dog         | 2011 | 9         | 200  |
| cat         | 2011 | 9         | 200  |
| dog         | 2011 | 8         | 188  |
| cat         | 2011 | 8         | 188  |
| dog         | 2011 | 7         | 301  |

Notice, total does not change per category.

Thanks for any help.

I found the resolution. But I'll still reward the first person who responds with a correct solution.
michaelminter over 5 years ago
Tags
MySQL
sql

Crowdsource coding tasks.

0 Solutions