Recordset counting number of records per month

Databases
1

Hi,

I’m not sure if this is possible. I’ve got a “news” section on my site and each news article has a “created date”. I want to build an “archive” style navigation like you see in blogs where you see:

June 2010 (4 articles)
May 2010 (12 articles)
April 2010 (15 articles)
and so on for the last 12 months.

The only way I know how to build this is to manually create a separate recordset for each line - doing a count for each fixed month (but this would mean I’d have twelve seperate database connections which seems bad practise).

However I wondered whether it was possible to create a repeating recorset that can work out the month and year (from the yyyy-mm-dd news article date) and count how many records in that month and then loop to the previous month and so on for the last 12 months.

The database table and (relevant) field info are shown below:

table = tblnewsitem
fields = newsId, newsdate
info: newsId is the unique ID, newsdate is the date of the article in yyyy-mm-dd format and is stored as a DATE.

So, for instance, if I wanted a list of ALL the news articles in reverse date order I would use the following:

“SELECT newsId, newsdate FROM tblnewsitem ORDER BY newsdate DESC”

I know I’ve not explained this terribly well but hopefully someone can help or tell me if it’s not possible.

Many thanks.

2

Hi, me again,

Would it be possible to restrict the results to the current year, rather than the last 12 months as per my original post.

i.e.

SELECT YEAR(newsdate) AS yr
, MONTH(newsdate) AS mth
, DATE_FORMAT(newsdate,‘%M %Y’) AS display_date
, COUNT(*)
FROM tblnewsitem
WHERE yr = YEAR(CURRENT_DATE)
GROUP
BY yr,mth
ORDER
BY yr DESC, mth DESC

Would this work?

3

thank you very much - it works great!

4

No. Think about it this way: if it’s January 2010, YEAR(CURRENT_DATE) returns 2010. WHERE yr = 2010 will return… January. Not the last 12 months.

Try WHERE (yr = YEAR(CURRENT_DATE) - 1 AND mth > MONTH(CURRENT_DATE)) OR (yr = YEAR(CURRENT_DATE) AND mth <= MONTH(CURRENT_DATE))

(The last mth <= MONTH(CURRENT_DATE) check on the end is unnecessary, unless you post things that will happen in the future ahead of time… if that makes sense.)

5

if you guys are referring to the yr and mth column aliases i created in post #2, you can’t use those in the WHERE clause

:slight_smile:

the most efficient way to get rows for the current year is

WHERE newsdate >= CURRENT_DATE - INTERVAL DAYOFYEAR(CURRENT_DATE)-1 DAY

:slight_smile:

6 
SELECT YEAR(newsdate) AS yr
     , MONTH(newsdate) AS mth
     , DATE_FORMAT(newsdate,'&#37;M %Y') AS display_date
     , COUNT(*)
  FROM tblnewsitem 
 WHERE newsdate >= CURRENT_DATE - INTERVAL 1 YEAR
GROUP
    BY yr,mth
ORDER
    BY yr DESC, mth DESC

the query uses yr and mth for sorting purposes, you would use display_date for display purposes