Display Archive Posts from Wordpress using SQL

Question

I am trying to display the 'Archived Posts' from the WordPress Database like this (but using ColdFusion to display the data) and need help on the SQL query.

Example:

January 2019 (1 post)
December 2018 (3 posts)
September 2017 (1 post)

My query looks like this:

SELECT      wp_posts.post_date, post_type, post_status
FROM        wp_posts
WHERE       wp_posts.post_type = "post"
AND         wp_posts.post_status = "publish"
GROUP BY        YEAR(post_date), MONTH(post_date) DESC

That gives me the following error:

"Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'dnr_wordpress.wp_posts.post_date' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by"

Not the person who down voted, so can't say for sure, but it could be because you are asking multiple questions, which you shouldn't do. (Stack Overflow's goal is to be a library of questions and answers that help not only the OP, but also people having the same problem in the future. The odds are really low that someone has the exact two problems that you have.) — Ivar
That being said, your main question (as far as I can tell) could be a duplicate of Error related to only_full_group_by when executing a query in MySql. — Ivar
Thanks, Ivar. I have edited my question to only be about the SQL. Also, I did see that post you mentioned before asking this question; I was unable to solve it based on that solution. — Blue Da Noob
The problem is that you can't select things you don't have in your group by. Because if you group by year and month, multiple records might be grouped together so the DB doesn't know which post_date to select. You could try something like SELECT YEAR(post_date) as year, MONTH(post_date) as month, COUNT(ID) as nr_of_posts ... — Ivar

Blue Da Noob Blue Da Noob · Accepted Answer · 2019-02-03T20:45:45

Thanks to Ivar, this query worked for me:

    SELECT      YEAR(post_date) as year, MONTH(post_date) as month, COUNT(ID) as nr_of_posts
    FROM        wp_posts

    WHERE       wp_posts.post_type = "post"

    AND         wp_posts.post_status = "publish"


    GROUP BY YEAR(post_date ) , MONTH( post_date );

Display Archive Posts from Wordpress using SQL

1 Answers