Aggregate Count Function
05 June 2008 Tips  Marek Sienkiewicz
Technorati Logo Technorati  Delicious Logo Del.icio.us  Dig Logo Digg  Blogflux Logo Blogflux
Bubble Image
I needed to show a count of entries against months in my archive list. My database table uses the DateTime function for the date field. This lists entries against the day, month year and time, for example 04/05/2008 03:34:45.

My previous blog entry for March showed that using

SELECT DISTINCT DateName(Month,Date) AS theMonth,
DatePart(Year,Date) AS theYear,
Month(Date) AS theMonth
FROM myTable
ORDER BY DatePart(yyyy,Date) DESC,
month(Date) DESC

listed the months in chronological order, however  I found it extremely difficult to incorporate a count using a derivedTable and still maintain a logical order for the months.

After hours of effort and research the following solution was arrived at.

SELECT dateAdd(mm,DateDiff(mm,0,date),0) AS date,
COUNT(date) From myTable
GROUP BY DateAdd(mm,DateDiff(mm,0,date),0)
ORDER BY date DESC

To format the date and only show the month and year I used

<%#Eval(“date”,”{0:y}%> (<%#Eval(“Column1”)%>





Please post your comments:

Name

Email
                           

   

Enter the text you see in the box:

Captcha Image