I'm looking for a "simple" to understand query that will quary all of the cities and areas (City Field) in a given database by specific date ranges to provide the MEDIAN sale price. The number of transactions are simply the total record count in the query per city.
I can do this manually but it takes for ever!
I've looked through the examples here but most are drastically complex.
There is not a simple way to calculate the median in SQL - at least not one that is obvious if you are not really quite comfortable with set based logic and SQL. There are also a couple of variants - I forget the names of them but if there are an even number of qualifying rows then you need to decide which value is the median.
Why do you need a simple version rather than one that simply "works"?
Access' SQL seems to be difficult for me to grasp.
Many times I see very complex answers to questions in the forums and then a very simple solution that I can grasp almost immediately. (Simple mind I suppose).
With Access it appears there is more than one way to accomplish the same goal.
In answer, "Simply" works - yes!
Where there are an even number of records I need to calculate the average of the two records (SalePrice field) in the middle of the table which becomes the MEDIAN.
the average of the two becomes the median
An odd number of records is easier (SalePrice = ?) or some kind of variable assignemt I think.
CCCCC = median/the same number of records above and below the median record
Here is the manual query I use.
SELECT TblSoCalMLS_Download.SALESPRICE, TblSoCalMLS_Download.CITY
WHERE (((TblSoCalMLS_Download.CITY)=[Enter City Code]) AND ((TblSoCalMLS_Download.PROPSUBTYPE)="PROPCONDO") AND ((TblSoCalMLS_Download.LISTSTATUS)="closd") AND ((TblSoCalMLS_Download.OFFMARKETDATE)>=#3/1/2008# And (TblSoCalMLS_Download.OFFMARKETDATE)<=#3/31/2008#))
ORDER BY TblSoCalMLS_Download.SALESPRICE;
I have to enter every city code one at a time and then do the averaging by hand if there are an even number of records.
Put a string variable in your query instead of [Enter City Code] to let you put the cities in your query by code, then loop through your cities and run the Rogue's function giving the cities to it.
you can put the resaults of function in an aray and then print it out.
<<Never Walk on the Traveled Path, Because it only Leads you to where the Others have been.>>
You can modify that to accept a WHERE clause and loop through it without too much difficulty.
Another method (second question of the day that can be solved this way... odd) would be to assign an arbitrary rank to every listing in your table, then take a look at the min and max rank values and see if the current rank is in the middle.
Also, you're not trying to do this over RETS are you?