SQL - monthly average, not average daily

Question

I have a table with values ​​containing 3 columns - Location, value, date

I want to work out the average value per month

still i

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

This returns averages, but the value is entered daily, so I have an average value per day, not a month, how can I achieve a monthly average?

Answer 1

try the following:

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

Answer 2

You can use the following if you want to group only a month:

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

You can even use GROUPING SETS , which will be GROUP BY Month, year, location, and then give you a common ground for everyone:

 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

Answer 3

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