COUNT function and MYSQL

Westside2004
Westside2004 used Ask the Experts™
on
Hi,

I'm using mysql and trying to write a query that returns the month and year along with a count of the number of blog posts for that year.  I am not using WordPress, just for reference.  

Here is my query so far which returns the month and year, but I need to get the number of blog posts for each month/year combination.   I tried using the COUNT() function, but I am not getting the right counts.  It seems to be counting each individual row, but I need to count the group of rows for a given month/year.

SELECT DISTINCT CONCAT( MONTHNAME(date_created), ' ' , REPLACE(YEAR(date_created),',','')) AS post_month_year,
LOWER(MONTHNAME(date_created)) AS post_month,
LOWER(REPLACE(Year(date_created),',','')) AS post_year
FROM blog_post

Open in new window


The only columns needed should be post_id, date_created to do this it seems, I just can't get the query correct.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dave BaldwinFixer of Problems
Most Valuable Expert 2014

Commented:
Are you getting an error message?
Distinguished Expert 2017

Commented:
Please provide a sample of data

One option deals with select representation,count(representation) from table
Where limiting if any
Group by representation
In your query, you do not have a Counter (count()() is missing.
Using distinct ....... Might..
SELECT CONCAT( MONTHNAME(date_created), ' ' , REPLACE(YEAR(date_created),',','')) AS post_month_year,
LOWER(MONTHNAME(date_created)) AS post_month,
LOWER(REPLACE(Year(date_created),',','')) AS post_year,
count(*)
FROM blog_post
GROUP BY post_year,post_month
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

would you mind closing the question ?
feel free to ask for help if that did not do the trick.
feel free to reqest deletion of the question if that is what you see fit.
feel free to post your own solution and accept it if you found a different one.
recommend: delete the question and revoke asker's privilege to ask a new one for a month unless a fee is paid

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial