SQL select statement with increment
- by Matt
Currently I'm using a for statement in PHP to get all the months for this SQL statement, but would like to know if I can do it all with SQL.
Basically I have to get the average listing price, and the average selling price for each month going back 1 year where the sellingdate = the month.
simple with PHP, but that creates 12 database hits.
I'm trying the sql statment below, but it returns listings totally out of order
SELECT
avg(ListingPrice),
avg(SellingPrice),
count(ListingDate),
DATE(SellingDate) as date,
MONTH(SellingDate) as month,
YEAR(SellingDate) as year
FROM `rets_property_resi`
WHERE Area = '5030'
AND Status = 'S'
AND SellingDate
Output:
867507.142857 877632.492063 63 1996-12-24 12 1996
971355.833333 981533.333333 60 1997-11-18 11 1997
949334.328358 985453.731343 67 1997-10-23 10 1997
794150.000000 806642.857143 70 1996-09-20 9 1996
968371.621622 988074.702703 74 1997-08-21 8 1997
1033413.366337 1053018.534653 101 1997-07-30 7 1997
936115.054795 962787.671233 73 1996-06-07 6 1996
875378.735632 906921.839080 87 1996-05-16 5 1996
926635.616438 958561.643836 73 2010-04-13 4 2010
1030224.472222 1046332.291667 72 2010-03-31 3 2010
921711.458333 924177.083333 48 1997-02-28 2 1997
799484.615385 791551.282051 39 1997-01-15 1 1997
As you can see, it pulls from random dates, I need to to pull from 2010-03, 2010-02, 2010-01, etc...
any help is appreciated!
