CODEWITHSUNDEEP
mysqlwordpress
Ash
Ash

Reputation: 225

MYSQL join with Group by - wordpress

I have a custom post type in wordpress where I want to get count of posts which are grouped month wise by a custom date field. My output should be

Month  CountofPost
April  1
August 2

My SQL code so far is

SELECT monthname(met.meta_value), count(pst.id) AS NumberOfPosts
from  wp_postmeta met 
inner join wp_posts pst 
on met.post_id = pst.ID
where pst.post_type = 'custom_post_type'
and met.meta_key = 'start_date'
and pst.post_status = 'publish'
group by meta_value

However, the output of above code is

Month  CountofPost
April  1
August 1
August 1

What am i doing wrong?

Upvotes: 1

Views: 53

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64496

Change your group by clause to group by monthname(met.meta_value)

select monthname(met.meta_value), count(pst.id) AS NumberOfPosts
from  wp_postmeta met 
inner join wp_posts pst on met.post_id = pst.ID
where pst.post_type = 'custom_post_type'
    and met.meta_key = 'start_date'
    and pst.post_status = 'publish'
group by monthname(met.meta_value)

Upvotes: 1

Related Questions