SQL select statement with increment
Posted
by Matt
on Stack Overflow
See other posts from Stack Overflow
or by Matt
Published on 2010-04-30T03:05:32Z
Indexed on
2010/04/30
3:07 UTC
Read the original article
Hit count: 377
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 SellingDateOutput:
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!
© Stack Overflow or respective owner