# PHP - SQL help needed
**URL:** https://forum.kirupa.com/t/php-sql-help-needed/59780
**Category:** programming
**Created:** [August 3, 2004, 8:13pm UTC](https://forum.kirupa.com/t/php-sql-help-needed/59780 "2004-08-03T20:13:03Z")
**Posts on this page:** 7
**Page:** 1
### Author:  [@Flashmatazz](https://forum.kirupa.com/u/Flashmatazz)
#### Post date: [August 3, 2004, 8:13pm UTC](https://forum.kirupa.com/t/php-sql-help-needed/59780/1 "2004-08-03T20:13:03Z")
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:
```php
$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.
---
### Author:  [@system](https://forum.kirupa.com/u/system)
#### Post date: [August 3, 2004, 8:36pm UTC](https://forum.kirupa.com/t/php-sql-help-needed/59780/2 "2004-08-03T20:36:04Z")
use LIMIT 0, 2 or LIMIT 0,3 ( I forgot how it goes) in the end of the SQL statement
---
### Author:  [@system](https://forum.kirupa.com/u/system)
#### Post date: [August 3, 2004, 9:12pm UTC](https://forum.kirupa.com/t/php-sql-help-needed/59780/3 "2004-08-03T21:12:08Z")
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:
```php
$lowest = mysql_result($result, 0);
$highest = mysql_result($result, $num-1);
$total -= ($lowest + $highest);
```
Anyway, I might look into the LIMIT statement tomorrow. Thanks.
---
### Author:  [@system](https://forum.kirupa.com/u/system)
#### Post date: [August 4, 2004, 5:46am UTC](https://forum.kirupa.com/t/php-sql-help-needed/59780/4 "2004-08-04T05:46:06Z")
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.
---
### Author:  [@system](https://forum.kirupa.com/u/system)
#### Post date: [August 4, 2004, 8:52am UTC](https://forum.kirupa.com/t/php-sql-help-needed/59780/5 "2004-08-04T08:52:12Z")
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
```php
IF (COUNT(field) >= 3) etc......
```
but this isn’t proper SQL syntax
I also tried
```php
"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. 🙂
---
### Author:  [@system](https://forum.kirupa.com/u/system)
#### Post date: [August 4, 2004, 3:45pm UTC](https://forum.kirupa.com/t/php-sql-help-needed/59780/6 "2004-08-04T15:45:36Z")
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:
```auto
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)
---
### Author:  [@system](https://forum.kirupa.com/u/system)
#### Post date: [August 4, 2004, 8:08pm UTC](https://forum.kirupa.com/t/php-sql-help-needed/59780/7 "2004-08-04T20:08:35Z")
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: