SELECT datetime MONTH and count

saidbakr

Hi,

I have a table named by article which has some fields. Currently, I meant by two fields, id which is an auto incremental int and pub_date which is datetime.

I want to have an SQL query that selects month and the count of articles in each month. I have wrote the following SQL to do this task:

SELECT DISTINCT(MONTH(article.pub_date)), COUNT(a.id)
FROM article, article as a
WHERE MONTH(article.pub_date)  = MONTH(a.pub_date) 
GROUP BY article.pub_date

The above query do the task. However, I think that its performance is very weak. In other word it takes relatively long time.

My question is there any other alternative query to do this task?

NogDog

Seems to me you simply should be able to do:

SELECT MONTH(pub_date) AS pub_month, COUNT(id) AS num_articles
FROM article
GROUP BY pub_month

saidbakr

Thank you it is very good answer. However, I would like to understand the rule of GROUP BY. I tried the manual, but I could not catch the concept clearly. If you have any idea or explanation about it, I will be appreciated.

NogDog

GROUP BY column_name says to merge rows that have the same value for "coumn_name" into one row. COUNT() is one of a set of function that work with GROUP BY. In its case, it counts the number of rows that were grouped into each result row.