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.

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…

(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

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