get a count of stories for each category by year/month

story-count-by-category-and-month.sql

SELECT t.term_id, REPLACE(t.name, '&', '&') as name, COUNT(p.ID) as post_count, YEAR(post_date) as year, MONTH(post_date) as month
FROM wp_terms t
INNER JOIN wp_term_taxonomy tax ON t.term_id = tax.term_id
INNER JOIN wp_term_relationships r ON tax.term_taxonomy_id = r.term_taxonomy_id
INNER JOIN wp_posts p ON r.object_id = p.ID
WHERE tax.taxonomy = 'category' and YEAR(post_date) >= YEAR(CURDATE()) and p.post_type = 'post' and p.post_status = 'publish'
GROUP BY term_id, YEAR(post_date), MONTH(post_date)
ORDER BY YEAR(post_date) DESC, MONTH(post_date) DESC, t.name ASC;

If we want all stories published this year but include or exclude desired categories, we can do it like this:

Categories to exclude:

• glean
• letters
• community voices
• imp
• mnopedia
• twin cities business
• from other nonprofit media
• minnpost picks

SELECT t.term_id, REPLACE(t.name, '&', '&') as name, COUNT(p.ID) as post_count, YEAR(post_date) as year
FROM wp_terms t
INNER JOIN wp_term_taxonomy tax ON t.term_id = tax.term_id
INNER JOIN wp_postmeta m ON REPLACE( REPLACE( m.meta_value, 'a:1:{s:8:"category";s:5:"', '' ), '";}', '' ) = t.term_id
INNER JOIN wp_posts p ON m.post_id = p.ID
WHERE tax.taxonomy = 'category' and YEAR(post_date) >= YEAR(CURDATE()) and p.post_type = 'post' and p.post_status = 'publish' and m.meta_key = '_category_permalink'
AND t.term_ID NOT IN (55575, 55623, 55577, 55590, 55619, 55622, 55630, 55628)
GROUP BY term_id, YEAR(post_date)
ORDER BY YEAR(post_date) DESC, t.name ASC;

Categories to include for only opinion:

• letters
• community voices

SELECT t.term_id, REPLACE(t.name, '&', '&') as name, COUNT(p.ID) as post_count, YEAR(post_date) as year
FROM wp_terms t
INNER JOIN wp_term_taxonomy tax ON t.term_id = tax.term_id
INNER JOIN wp_postmeta m ON REPLACE( REPLACE( m.meta_value, 'a:1:{s:8:"category";s:5:"', '' ), '";}', '' ) = t.term_id
INNER JOIN wp_posts p ON m.post_id = p.ID
WHERE tax.taxonomy = 'category' and YEAR(post_date) >= YEAR(CURDATE()) and p.post_type = 'post' and p.post_status = 'publish' and m.meta_key = '_category_permalink'
AND t.term_ID IN (55577, 55623)
GROUP BY term_id, YEAR(post_date)
ORDER BY YEAR(post_date) DESC, t.name ASC;

If we just want the totals for the comment above, we can do this:

excluding stuff:

SELECT COUNT(p.ID) as post_count, YEAR(post_date) as year
FROM wp_terms t
INNER JOIN wp_term_taxonomy tax ON t.term_id = tax.term_id
INNER JOIN wp_postmeta m ON REPLACE( REPLACE( m.meta_value, 'a:1:{s:8:"category";s:5:"', '' ), '";}', '' ) = t.term_id
INNER JOIN wp_posts p ON m.post_id = p.ID
WHERE tax.taxonomy = 'category' and YEAR(post_date) >= YEAR(CURDATE()) and p.post_type = 'post' and p.post_status = 'publish' and m.meta_key = '_category_permalink'
AND t.term_ID NOT IN (55575, 55623, 55577, 55590, 55619, 55622, 55630, 55628)
GROUP BY YEAR(post_date)
ORDER BY YEAR(post_date) DESC;

including opinion only:

SELECT COUNT(p.ID) as post_count, YEAR(post_date) as year
FROM wp_terms t
INNER JOIN wp_term_taxonomy tax ON t.term_id = tax.term_id
INNER JOIN wp_postmeta m ON REPLACE( REPLACE( m.meta_value, 'a:1:{s:8:"category";s:5:"', '' ), '";}', '' ) = t.term_id
INNER JOIN wp_posts p ON m.post_id = p.ID
WHERE tax.taxonomy = 'category' and YEAR(post_date) >= YEAR(CURDATE()) and p.post_type = 'post' and p.post_status = 'publish' and m.meta_key = '_category_permalink'
AND t.term_ID IN (55577, 55623)
GROUP BY YEAR(post_date)
ORDER BY YEAR(post_date) DESC;

To do a better group by over several months (for a year, for example) do this:

SELECT t.term_id, REPLACE(t.name, '&', '&') as name, COUNT(p.ID) as post_count, EXTRACT(YEAR_MONTH FROM post_date) as yearmonth
FROM wp_terms t
INNER JOIN wp_term_taxonomy tax ON t.term_id = tax.term_id
INNER JOIN wp_postmeta m ON REPLACE( REPLACE( m.meta_value, 'a:1:{s:8:"category";s:5:"', '' ), '";}', '' ) = t.term_id
INNER JOIN wp_posts p ON m.post_id = p.ID
WHERE tax.taxonomy = 'category' and EXTRACT(YEAR_MONTH FROM post_date) >= 202110 and p.post_type = 'post' and p.post_status = 'publish' and m.meta_key = '_category_permalink'
AND t.term_ID NOT IN (55575, 55623, 55577, 55590, 55619, 55622, 55630, 55628)
GROUP BY term_id, EXTRACT(YEAR_MONTH FROM post_date) DESC
ORDER BY EXTRACT(YEAR_MONTH FROM post_date) DESC, t.name ASC;