SQL Help with Sum and AVG

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
avstudio1
Forum Newbie
Posts: 13
Joined: Mon Nov 07, 2005 5:20 pm

SQL Help with Sum and AVG

Post 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.
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post 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.
Post Reply