Grouping help 1

[TechJatt]

the relevent fields: state (nvarchar), date (date), x (bool)

basically, i need a query that will find the number of records where x=1, and display the total for each month and year, so something like:

state year month count
------ ------ ------- -------
VA 2002 June 5
VA 2002 Nov 35
NY 1999 Apr 344
NY 1999 Oct 66
NY 2000 Jan 8

so, i made this statement:
select state, datepart(year,date) as "year",
datename(month,date) as "month",
"count"=count(*)
from table
where x=1
group by state, date

problem is, it makes me group on date, although im only concerned with the month and year...so it gives me results like:
state year month count
------ ------ ------- -------
VA 2002 Nov 20 (date: 11-5-02)
VA 2002 Nov 5 (date: 11-12-02)
VA 2002 Nov 10 (date: 11-25-02)

so basically, since the full dates are different for these groups, it is grouping them separately, but i am not concerned with the specific day and dont want this to separate the groups.

is there anyway i can split up the date so that i can group by the month and year individually? i know i can create a view, and query the view to get my results, but i want a more 'elegant' 1 line solution.


any help would be greatly appreciated!!

and please let me know if more clarification is needed...

 

[mrdenny]

Change your group by to:
group by state, datepart(year,date), datename(month,date)

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[TechJatt]

simple enough...why didnt i think of that??

thanks alot though, worked like a charm!