Calculating range and frequency from age column

[lilwizz770]

I have a table applicants and one of the columns is age, i want to be able to calculate the age range, frequency and variation. Is there a way this can be achieved?

[phorce]

What do you mean 'Frequency of Age'? Frequency means something else.. Do you know the how frequent a particular age is given?

[mmcdonald]

Lilwizz, you can calculate frequency in a loop. Set $count = 0 and at the end of each loop increment count by one : $count++. In the middle of the loop you'll need to execute a query counting the number of rows where age = $count. You can of course limit the loop to 100 as I doubt you'll ever have anyone over that age? Store the result of each loop in an array and then you'll be able to count frequency. Make sense?

For range you just need to retrieve the lowest and highest value in the age column.

I don't know what you mean by variation..?

The only reason I'm not giving you the code straight up is that you've shown no evidence of trying to figure it out for yourself. Post some code as your attempt and we can help you make improvements and get it working.

The problem with 'age' columns is that they 'age'. Haw haw. Seriously, they're out of date as soon as you insert them. You should have a date of birth and calculate the age from that when required.

The functionality you require is possible. However, you may find it easier to process in php. So, collect all entries into an array and work off that.

$data = array(min($ageArray), max($ageArray), ...);

[mmcdonald]

+1 to Diafol, awesome point :)

Here are a few examples of pasted-up functions:

function mean($data)
{
    $count = count($data); 
    $sum = array_sum($data); 
    return $sum / $count;   
}

function variance($data, $precision= NULL)
{
    $totalAbsoluteDiffs = array();
    $mean = mean($data);
    foreach($data as $item)
    {
        $totalAbsoluteDiffs[] = pow(($item - $mean),2);
    }
    if($precision && is_int($precision))
    {
        return number_format(mean($totalAbsoluteDiffs),$precision);
    }else{
        return mean($totalAbsoluteDiffs);
    }
}

function frequency($data)
{
    $freq = array_count_values($data);
    ksort($freq);
    return $freq;
}

$data = array(1,2,3,5,4,6,4,3,2,3,4,5);

echo "<h3>Raw Data</h3><pre>";
print_r($data);
echo "</pre>";

echo "<h3>Frequency</h3><pre>";
print_r(frequency($data));
echo "</pre>";

echo "<h3>Range</h3><pre>";
echo min($data) . ' to ' . max($data);
echo "</pre>";

echo "<h3>Variance</h3><pre>";
echo variance( $data, 2 );
echo "</pre>";

$data is hard-coded here, but you'd get this from your DB though...

$data = array();
$result = mysql_query("SELECT ... AS age FROM users");
while($d = mysql_fetch_assoc($result))
{
    $data[] = $d['age']; 
}

That little bit uses the soon-to-die-a-horrible-death mysql_* functions, just for clarity - aim for mysqli or PDO. You could use different extraction functions here - especially if just retrieving a single column.