PHP - SQL help needed

[Flashmatazz]

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.

[hamza84]

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

[Flashmatazz]

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.

[Hans Kilian]

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.

[Flashmatazz]

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. :)

[Hans Kilian]

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)

[Flashmatazz]

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: