How about something like:

SELECT
    DatePart("m", Field1) + '.' + DatePart("y", Field1) AS MonthYear,
    Count(Field1)
FROM
    Table1
GROUP BY
    DatePart("m", Field1)

Cheers
Ken

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
From: "Sander van der Moolen" <svan_der_moolen@h...>
Subject: [sql_language] Re: About the mssql CONVERT function


: I was looking for a statistical function to count the number of users per
month, over a period of at least six months. So the statement had to return
me (a minimum of) 6 rows: one count number and the corresponding month. But
the values in the [date_column] are set like [mm/dd/yy hh/mm/ss PM], so what
I needed to do first is to single out the month and year part from the
value. This way i was able to convert the output of the statement into a bar
chart. Here's the entire statement:
:
: SELECT SUBSTRING(CONVERT(CHAR, [date_column], 104), 4, 7) "Month",
COUNT(DISTINCT([user_column])) "No. of users" where [audit_column]='logon'
GROUP BY SUBSTRING(CONVERT(CHAR, [date_column], 104), 4, 7)
:
: This gives me an output like:
:
: 01.2001  34
: 02.2001  20
: 03.2001  57
: 04.2001  38
: 05.2001  64
: 06.2001  42
: etc. etc.
:
: But if you have an easier way of getting the same results, please let me
know.
:
: Thanks!
:
: Sander

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~