COUNT function and MYSQL

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.
LVL 1
Westside2004Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dave BaldwinFixer of ProblemsCommented:
Are you getting an error message?
arnoldCommented:
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..
skullnobrainsCommented:
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
OWASP: Threats Fundamentals

Learn the top ten threats that are present in modern web-application development and how to protect your business from them.

skullnobrainsCommented:
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.
skullnobrainsCommented:
feedback ?
skullnobrainsCommented:
recommend: delete the question and revoke asker's privilege to ask a new one for a month unless a fee is paid
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.