'SQL - monthly average rather than daily average

I have a table called values that contains 3 columns - Location, value, date

I want to work out the average value per month

so far I have 

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

This returns the average values but a value is entered on a daily basis so I have an average per day rather than per month, how can I achieve a monthly average?

sqlsql-serversql-server-2008tsql


Solution 1:[1]

try this:

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

Solution 2:[2]

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

Solution 3:[3]

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

Solution 4:[4]

Can also do it as the following if you want to combine the dates into one column with the first of the month as the day.

SELECT    Location,
          Avg(value),
          DateFromParts(Year(date), Month(date) , 1) AS FirstOfMonthDate
FROM      Value
GROUP BY  Location,
          DateFromParts(Year(date), Month(date) , 1)

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Nat
Solution 2
Solution 3 podiluska
Solution 4 timdice

Related Questions