SQL Help with Sum and AVG
Posted: Fri Apr 28, 2006 9:54 am
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:
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.
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.