I have a challanging query problem

[nikos101]

I have a challanging problem. I have a datetime column and a numerical column. I need a query that returns a set of the average of the numerical column per month.

Note my data is spread over years so I need to group by mm yy

[nikos101]

The best I can come up with so far is:

SELECT SUBSTRING(CONVERT(VARCHAR(8), valueDate, 3), 4, 5) AS mmyy
,avg(rate)
FROM [commercial].[dbo].[OTT_DEALS]
group by valueDate

[nikos101]

Wait a minute I think this is it:

SELECT SUBSTRING(CONVERT(VARCHAR(8), valueDate, 3), 4, 5) AS mmyy
,avg(rate)
FROM [commercial].[dbo].[OTT_DEALS]
group by SUBSTRING(CONVERT(VARCHAR(8), valueDate, 3), 4, 5)