Calculation Query & Some Looping

[transfield]

Hello,
I've got a table called condo03. In this table there are 3 fields called date, price & full_add respectively. I want to:-

1. Query the full_add field using a keyword that I specify. Then group all the dates together in the date field. Then calculate the median price in the price field according to the individual date groups. As such, if there are 10 different dates in the date field, then there should be 10 groups created & 10 different median prices generated.

I've written the code below to the best of my ability & I hope that you can edit it for me. The part that is not working is the calculation of the median price according to individual date groups. At present it is calculating the median in total regardless of the number of date groups.

Thanks a lot for your help.

PHP Code:

<?php 
$query1=("SELECT price FROM condo03 WHERE (full_add like '%$Text_Box_1%' OR full_add like '%$Text_Box_2%' OR full_add like '%$Text_Box_3%' OR full_add like '%$Text_Box_4%') AND (status = '$status') AND (size >= '$Text_Box_15' AND size <= '$Text_Box_16')"); 
//$query2 is working fine. 
$query2=("SELECT distinct(date), COUNT(date) AS count FROM condo03 WHERE (full_add like '%$Text_Box_1%' OR full_add like '%$Text_Box_2%' OR full_add like '%$Text_Box_3%' OR full_add like '%$Text_Box_4%') AND (status = '$status') AND (size >= '$Text_Box_15' AND size <= '$Text_Box_16') GROUP BY 'date' ASC"); 

$result1=mysql_query($query1); 
$num1=mysql_num_rows($result1); 
$result2=mysql_query($query2); 
$num2=mysql_num_rows($result2); 
//the median calculation starts here. The code works fine. 
$thearray=array(); 
while ( $row=mysql_fetch_array($result1,MYSQL_NUM) ) { 
$thearray[]=$row[0]; 
} 

$num0=count($thearray); 
if ($num0 % 2) { 
$median = $thearray[floor($num0/2)]; 
} else { 
$median = ($thearray[$num0/2] + $thearray[$num0/2 - 1]) / 2; 
} 
//The median calculation ends here. 
while ($row2 = mysql_fetch_assoc($result2)){ 
?> 
<table border="1" cellspacing="2" cellpadding="2"> 
<tr> 
<td style="border-style: solid; border-width: 1" bgcolor="#CCFFFF" align="left" width="165"><b><div align="center"><font size="4" face="Arial, Helvetica, sans-serif"><? echo "$row2[date]"; ?></font></div></td> 

<td style="border-style: solid; border-width: 1" bgcolor="#CCFFFF" align="left" width="165"><b><div align="center"><font size="4" face="Arial, Helvetica, sans-serif"><? echo "$median"; ?></font></div></td> 

<td style="border-style: solid; border-width: 1" bgcolor="#CCFFFF" align="left" width="165"><b><div align="center"><font size="4" face="Arial, Helvetica, sans-serif"><? echo "$row2[count]"; ?></font></div></td> 
</tr> 
</table> 
<?php 
} 
?>

[teknomage1]

I think you could calculate it all with a single query like this:

$query = "SELECT (MIN(price)+MAX(price))/2 as median FROM condo03 WHERE (full_add like '%$Text_Box_1%' OR full_add like '%$Text_Box_2%' OR full_add like '%$Text_Box_3%' OR full_add like '%$Text_Box_4%') AND (status = '$status') AND (size >= '$Text_Box_15' AND size <= '$Text_Box_16') GROUP BY 'date' ASC");

[transfield]

Wow, I did not know this was possible. Thanks. However, this looks like the calculation for average & not median. What does (MIN(price)+MAX(price))/2 mean anyway?

[sde]

what exactly is the formula to figure out median price?

[transfield]

The median is the centre number in an array of numbers after it has been sorted ascending or decending. It is not affected by the extremes. For example the median number from the array below is 4.5. The php code I'm using isn't perfect either but this is the best I could do.
1
2
3
2
6
67
8
9
6554
0

[sde]

if you're grouping by date, i don't think you should use 'distinct(date)'. i can't give you a great answer though without trial and error queries against your data.

i did lookup the calculation for median and it looks like you may be missing 1 thing. this page says to add 1 to the count of the array before dividing it by 2.

example for the array would be this:

PHP Code:

<?
$array = array(22, 33, 44, 55, 66, 77, 88, 99);

// array count + 1 then divided by 2 and rounded down.
// then subtract 1 to use with a zero based array
$array_key = floor((count($array)+1)/2)-1;

$median = $array[$array_key];
?>

that's how i understood it anyway. i don't think i could be much more help on the query though.

[transfield]

I appreciate your efforts. Thanks a lot & keep up the good work.

[teknomage1]

You got me, I did indeed cacluate the average value. Whoops. Here's a link on how to do median values as a single query: Build upon MySQL's statistics functions.

[transfield]

Thanks a lot, man. Much appreciated.