Standard Deviation and Median function?

[trifest]

I'm trying to do a Standard Deviation and Median calculation. anyway i can do this within a query such as

sele standarddeviation(col1), median(col1) from yada

or is there another way i can approach this. Thanks so much.

 

[ramani]

Hi

Wow.. i dont know about that.. Just kidding..

I only know that CALCULATE STD(nExpression) gives standrad deviation. CALCULATE AVG(nExpression) gives averages. Median.. I dont know, any function gives median.

You must be meaning a user built functions, StandardDeviation and Median trifest.

ramani
(Subramanian.G),FoxAcc, ramani_g@yahoo.com

 

[trifest]

how would i use the calculate std(nExpression)? i don't know how to invoke. would i use it within the SQL query? thanks so much for the help.

 

[rgbean]

Since STD and Median are aggregate functions, if they were available in an SQL Select they'd only work if you had a GROUP BY clause (like you need for AVG).

To use CALCULATE STD, you could do your SQL Select into a cursor, then do the CALCULATE on these resultant records. Note: The STD function in VFP uses the "offical" definition of this function, which doesn't always give the same results as Excel would using the same data. It's important to make who ever is using this data aware of this fact. (I had an "analyst" that basically said if it doesn't match Excel it's wrong - of course it did match the results in SPSS!)

To get the MEDIAN, use this cursor, and order it by (or index it on) the field you want the median of, divide the number of records by two, go to that record, and use this value. Note: This technique will only work if you have a true cursor, and not a filter set of records, as the RECCOUNT() will be wrong in the filter circumstance.

One other option is to use Automation, and load the data into Excel, and use it's built-in functions to calculate these values for you. (Note: my comments above!)

Rick