SQL moving average with calc of average price per square metre

Question

I'm trying to get a moving average for a set of real estate data that I can then graph, using MS SQL 2005.

Fields are: DateSold datetime, FloorSQM decimal(8,2), Price decimal(10,0)

What I need to do is get an average price per square metres of all the properties sold within each month as far back as we have data, which can be several years.

Therefore I need to Price/FloorSQM each property, and then apply a moving average to them for each month. Hope that makes sense.

I am completely stuck trying to figure this out, and googling endlessly is endlessly confusing.

I thought I found what I'm looking for in another post, but I keep getting "incorrect syntax near 'm'" when I try to compile it.

SELECT YEAR(DateSold) AS year, 
   MONTH(DateSold) AS month,
   ROUND(Euros/CONVERT(DECIMAL(10,2),FloorSQM),2) as avg
FROM
(select s.*, DATE_ADD(DTE, INTERVAL m.i MONTH) DateSold, m.i
FROM Property s
cross join (select 0 i union select 1 union select 2 union select 3 union 
         select 4 union select 5 union select 6 union select 7 union 
         select 8 union select 9 union select 10 union select 11) m) sq
WHERE DateSold <= curdate()
GROUP BY YEAR(DateSold), MONTH(DateSold)

When I have this working, I then need to introduce a WHERE clause so I can limit the properties found to certain kinds (houses, apartments) and areas (New York, Los Angeles, etc)

Can anyone lend a hand please? Many thanks for your help.

SQL Server 2005 isn't great at cumulative values, including moving averages. Do you have the ability to generate the monthly results and then to process those in your reporting environment to change them in to moving averages? — MatBailie, Jul 01 '14 at 15:53
What do you mean "apply a moving average to them for each month"? Can you provide sample data and expected results. — Gordon Linoff, Jul 01 '14 at 15:54
One problem is that you are mixing Syntax from MySQL and SQL Server - `DATE_ADD` is MySQL, `DATEADD` is SQL Server. `CurDate()` is MySQL, `GETDATE()` is SQL Server. — GarethD, Jul 01 '14 at 15:55

Hart CO · Accepted Answer · 2014-07-01T16:21:40.607

1

This should run, you were using some Mysql syntax:

SELECT YEAR(DateSold) AS year, 
       MONTH(DateSold) AS month,
       ROUND(Euros/CONVERT(DECIMAL(10,2),FloorSQM),2) as avg
FROM (SELECT s.*, DATEADD(MONTH,m.i,DateSold) DateSold2, m.i
      FROM Property s
      cross join (select 0 i union  select 1 union  select 2 union  select 3 union 
                  select 4 union  select 5 union  select 6 union  select 7 union 
                  select 8 union  select 9 union  select 10 union  select 11
                  ) m        
     )sq
WHERE DateSold<= GETDATE()
GROUP BY YEAR(DateSold), MONTH(DateSold)

Not sure if it returns the desired results.

Update: I thought DTE was a field name, you can't have DateSold referenced twice in your subquery, so I changed to DateSold2, not sure if you intend for the DATEADD() result to be used in all places where DateSold is used, you could also just remove DateSold from your subquery SELECT list.

edited Jul 01 '14 at 16:21

answered Jul 01 '14 at 15:56

Hart CO

34,064
6
48
63

Hi thanks for your kind help. I get this error however: "Invalid column name 'DTE'. The column 'DateSold' was specified multiple times for 'sq'." – TVRV8S Jul 01 '14 at 16:09
This is where I got the basis of my posted SQL: http://stackoverflow.com/questions/9784027/calculate-moving-averages-in-sql – TVRV8S Jul 01 '14 at 16:12
Oh, I thought `DTE` was a field name, it needs to be `DATEADD(unit_of_time, integer_to_add, date_field)` not sure which date you're trying to add. You'll have to alias the `DATEADD()` result field as something other than `DateSold` since that field already exists. – Hart CO Jul 01 '14 at 16:12
Thanks. I'm just getting more confused as I'm bodging code that I got from that other page, and I don't have the knowledge to take what you've said and apply it to the code to make it work. So I'm thinking I'll get the values out into a temporary table and then apply moving average rather than trying to do it all at once, which is far beyond my capabilities at the moment. Thanks for your help! – TVRV8S Jul 02 '14 at 09:17

score 1 · Answer 2 · answered Jul 01 '14 at 16:12

Try this:

SELECT YEAR(DateSold) AS year, 
   MONTH(DateSold) AS month,
   AVG(ROUND(Price/CONVERT(DECIMAL(10,2),FloorSQM),2)) as avg
FROM property p
GROUP BY YEAR(DateSold), MONTH(DateSold);

Note, changed the query from Euros to Price, as you specified in the schema.

The grouping of year, month is all you need to get the average of the price for that month. You were missing the aggregate function, AVG around your formula (though might be more accurate if ROUND was wrapped around AVG). I set up a quick sample at SQLFiddle.

SQL moving average with calc of average price per square metre

2 Answers2