>>>I have a table with a datetime field. I would like to count the number of records that exist per year to get a result set something like this:
>>>
>>>
>>>count year
>>>
>>>42 1994
>>>67 1995
>>> .
>>> .
>>> .
>>>234 2005
>>>42 2006
>>>
>>>
>>>Any assistance would be appreciated.
>>>
>>>Thanks,
>>>Einar
>>
>>
>>SELECT YEAR(datetimefield) ,count(*) from table group by YEAR(datetimefield)
>>
>
>Thank you very much for the prompt reply. The only problem I ran into is that I am querying a Paradox table using the Borland SQL Explorer. I was hoping that Paradox used "standard" SQL syntax but it doesn't like the YEAR(fieldname).
>Ohh well I might have to do it the hard way.
>
>Einar
try ODBC function
SELECT {fn YEAR(getdate())} y ,count(*) from table group by {fn YEAR(getdate())}
* or
SELECT {fn YEAR(getdate())} y ,count(*) from table group by y