PHP - SQL help needed

Hopefully someone can help me with this.

I have a selection from a mySQL database and I want to calculate the sum of the returned values, minus the highest and lowest value, meaning the selection should contain at least 3 values.
However, I’m not sure how I can do this.

Can I do this with only one SQL statement? And if so, how?

What I’m trying now is something like:


$query = "SELECT * FROM table WHERE field = '$myVar' ORDER BY field";
$result = mysql_query($query);
$num   = mysql_numrows($result);
if ($num >= 3){
  $total = 0;
  while ($row = mysql_fetch_object($result)) {
    $total += $row->field;
  }
}

This gets the sum of all retrieved records but I want to lose the min and max values.

Any ideas? Thanks.

use LIMIT 0, 2 or LIMIT 0,3 ( I forgot how it goes) in the end of the SQL statement

LOL, in the meantime I got it to work, but way more difficult than your solution.

After the while loop that got me the total, I added:


$lowest	= mysql_result($result, 0);
$highest = mysql_result($result, $num-1);
$total -= ($lowest + $highest);

Anyway, I might look into the LIMIT statement tomorrow. Thanks.

SELECT sum(field) - (min(field) + max(field)) FROM table

It will return one row with one column which contains the sum of all field values except the highest and lowest.

Thanks, just using an SQL statement indeed looks a lot prettier than what I have now.

One question though: let’s say the field only contains only 1 value, e.g. 4

SUM(field) equals 4, but so do MIN and MAX, so I end up with a value of -4.

So I guess I need something like


IF (COUNT(field) >= 3) etc......

but this isn’t proper SQL syntax

I also tried


"SELECT sum(field) - (min(field) + max(field)) FROM table WHERE field = '$myVar' AND COUNT(field)>= '3'"

This doesn’t work either unfortunately. So for the moment I’ll stick to what I’ve got, but if there’s a solution to do this within the SQL statement I’ll be happy to hear it. :slight_smile:

OK - I thought there’d always be 3 or more rows. So this brings up a new question: What will you do if there are 2 rows? Take the highest or the lowest - or maybe the average?

If you are on MySQL 4.0.0 or newer you can do something like this:


SELECT count( * ) AS c, sum( field ) AS n
FROM `table`
HAVING count( * ) = 1

UNION ALL

SELECT count( * ) AS c, max( field ) AS n
FROM `table`
HAVING count( * ) = 2

UNION ALL

SELECT count( * ) AS c, sum( field ) - ( min( field ) + max( field ) ) AS n
FROM `table`
HAVING count( * ) > 2

The UNION keyword isn’t supported in MySQL version 3. Anyway it’s not pretty… :slight_smile:

(I’m on MySQL version 3 myself, so I haven’t been able to test the SQL)

Wow, that’s a bit more complicated than I thought it would be

I guess I leave it as it is then :slight_smile:

Anyway, thanks for your explanation. I appreciate it :thumb: