SQL - monthly average rather than daily average

try this:

SELECT    Location,
          Avg(value),
          month(date),
          year(date)
FROM      Value
GROUP BY  Location,
          month(date),
          year(date)

You can use the following, if you want month only grouping:

SELECT Location, Avg(value) AvgVal, Month(date) Mnth
FROM Value
GROUP BY Location, Month(date)

You can even use GROUPING SETS, which will GROUP BY Month, year, location and then give you a total for all:

SELECT Location, 
  Avg(value) AvgVal, 
  Month(dt) Mnth,
  Year(dt) Yr
FROM yourtable
GROUP BY 
  GROUPING SETS((Month(dt), Year(dt), Location), (Location));

See SQL Fiddle with Demo

SELECT
     Location,
     year(date),
     month(date),
     Avg(value)
FROM
     Value
GROUP BY
     Location,
     year(date),
     month(date)