My data table has a few fields I use to calculate financial medians, one city at a time.
The fields in my data table (tblCARETSData) are . . .
County (Used to SELECT a county name)
City (Used to SELECT a city name)
ClosingDate (used to SELECT a date range)
ClosePrice (for the MEDIAN calculation)
BuildingSize (for the MEDIAN calculation)
Count (the number of records used in the median calculation)
Below is a query example I found on the Internet for financial median but for the life of me I cannot understand which field names to substitute in the query?
CASE WHEN COUNT(*)%2=1
ELSE (x.Hours+MIN(CASE WHEN y.Hours>x.Hours
FROM BulbLife x, BulbLife y
GROUP BY x.Hours
SUM(CASE WHEN y.Hours <= x.Hours
THEN 1 ELSE 0 END)>=(count(*)+1)/2 AND
SUM(CASE WHEN y.Hours >= x.Hours
THEN 1 ELSE 0 END)>=(count(*)/2)+1
My goal is to be able to run this (or similar query) and go through the table (tblCARETSData) calculating the MEDIANS (closeprice, buildingsize) and the count for all of the cities listed in the table.
See this page for the end result which I'm now doing one city at a time by hand.