[john6321]

Hi,

This may be a beginner/intermediate sql question, but I can't seem to figure out the answer. I'm hoping that someone could point me in the right direction, or share wtih me the sql to do what I want to do.

I have a table:
date_created (datetime)
some_number (int)

Let's say I have a bunch of data, spanning months and years of information. I would like to have a query that produces a report of how many numbers (rows) exist for each month. I'd like to give it a start and end date, and then it would report and segment the results into months. Also, it would be great if it could also segment by years...so if it was 2002-2004, then it would have 24 months listed.

An example result would be:
Month Count
------- ------
January 10
February 7
March 1
April 22


How would I do this with sql? I'm using a MS-SQL database (I know, I wish it were not MS, but that's what the non-profit group chose, so that's what I have to work with). So, I have nested sql statesments and use T-SQL if needed.

Any help would be awesome! Thanks in advance!

-J

[insha]

Don't know about MS-SQL but in mysql you run the following query (but the sql should be similar in MS-SQL):

select year(date_stamp), monthname(date_stamp), count(some_number)
from sample
group by monthname(date_stamp)
order by month(date_stamp)

This will yield the following output:

2005 January 31
2005 February 28
2005 March 31
2005 April 30
2005 May 31
2005 June 30
2005 July 31
2005 August 31
2005 September 30
2005 October 31
2005 November 30
2005 December 31

Cheers.