Page 1 of 1

SQL Help with Sum and AVG

Posted: Fri Apr 28, 2006 9:54 am
by avstudio1
I am trying to write an SQL statement in php (mysql) to get some unique results.

I need to sum fields only if thier individual value is greater than the average found in the current result.

For Example:

Code: Select all

$DayShiftDowntimeSQL = "SELECT SUM(RunAutoTime) AS DOWNTIME,
AVG(RunAutoTime) AS AVERAGETIME
FROM ester_perpart
WHERE TS BETWEEN '$DayShiftStart' AND '$DayShiftEnd'
GROUP BY DCM_ID
HAVING RunAutoTime > AVERAGETIME
";

but obviously this doesn't work very well.....

What I need is know the total sum of the [RunAutoTime] field only when each RunAutoTime is greater than the average for the shift.

Posted: Fri Apr 28, 2006 11:29 am
by GM
I've managed to do it in one query, but it's a bit ugly, and I'm not sure it's the simplest way (it was the first way that worked...)

Code: Select all

SELECT SUM(a.RunAutoTime) 
 FROM ester_perpart a,
 (SELECT AVG(RunAutoTime) as av FROM ester_perpart) b 
WHERE a.RunAutoTime > b.av
It might be better to find the average first, and use it in a second query to extract the values.

Edit: I missed out the BETWEEN statement, but it should be easy enough to put it back in.