Have Geek Brain

SQL select output incremented

I am currently 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 need to get the average listing price and the average selling price for each month is returned 1 year when saledate = month.

simple with PHP, but this creates 12 database hits.

I am trying to execute the sql file below but it returns lists completely 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 "less then" = 'NOW ()'
GROUP BY month
ORDER BY month desc
LIMIT 0.12

"less" is removed from the statute, so please replace "less" with the appropriate code

Exit:

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
936 115.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 is pulling from random dates, I need to pull from 2010-03, 2010-02, 2010-01, etc.

any help is appreciated!

+2


a source to share


1 answer


Try the following:



SELECT 
    avg(ListingPrice), 
    avg(SellingPrice),
    count(ListingDate),
    MONTH(SellingDate) as month,
    YEAR(SellingDate) as year
FROM `rets_property_resi` 
WHERE Area = '5030'
    AND Status = 'S'
    AND SellingDate <= NOW()
GROUP BY year, month
ORDER BY year desc, month desc
LIMIT 0,12
+2


a source






More articles:


All Articles