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