Order by aggregate function

[aas4mis]

I need to order the following by sumofrate.

SELECT [name], sum([pieces]) AS sumofpieces, sum([hours]) AS sumofhours, sum([pieces])/sum([hours]) AS sumofrate, sum([errors]) AS sumoferrors, [department]
FROM tblTempRate
WHERE name in (SELECT name 
               FROM tblTempRate 
               GROUP BY name, department 
               HAVING sum(hours)>4)
GROUP BY [name], [department]

Whenever I add "ORDER BY sum([pieces])/sum([hours])" to the end of the statement I get an "overflow" error. I've tried to change my sumofrate in the SELECT and ORDER BY clauses to "sum(nz([pieces],0))/sum(nz([hours],0))" in case this was some type of NULL error, but no go. Both pieces and hours are of type double, the only thing I can think of is too many decimal places in some of the results. Any ideas?

Help would be much appreciated. Thanks!!

[jimatqsi]

Don't forget, you can't divide by zero. Even if you're testing on sum(hours) and only accepting the ones >4.

Add .0001 to your divisor just to be sure you don't fall into that trap. But I don't think that would give you an overflow error, so you may have another problem.

Code out the divide by zero problem and then see if you are still getting the overflow error. If you are, make some tests to narrow down the problem. Change the sum([pieces]) to 1 or some other constant and see if the problem still happens. Then change it back and change sum([hours]) to 1 or some other constant and see if you get the problem.

If you think the scale is the problem you could put the division into a Round() statement to control the number of decimal places returned.

Jim

[aas4mis]

Well, if that isn't karma I don't know what is. Jim, nice to hear from you again, just replied to you this morning. :)

Thanks for leading me in the right direction. It was a division by zero problem. Here's the correct code.

SELECT [name], sum([pieces]) AS sumofpieces, sum([hours]) AS sumofhours, sum([pieces])/IIf(sum([hours])=0,0.001,sum([hours])) AS sumofrate, sum([errors]) AS sumoferrors, [department]
FROM tblTempRate
WHERE name in (SELECT name                             
               FROM tblTempRate
               GROUP BY name, department
               HAVING sum(hours)>4)
GROUP BY [name], [department]
ORDER BY sum([pieces])/IIf(sum([hours])=0,0.001,sum([hours])) DESC;
 

Once again the scripts,.. err.. bytes.com solved the problem.

[jimatqsi]

Great, glad to be of help. However, I'm not sure you want to substitute a value of .001 for a zero in your equation. It works mathematically, but probably not logically.

Perhaps you want instead a 1. I mean, what happens if you divide your number of pieces by that very small value? You get a very large value in return, and that may skew your dataset. Or maybe it's okay like that, I can't know.

Jim

[aas4mis]

@jimatqsi
Good catch. Didn't notice because any time hours is 0, pieces will be 0. But changed to divide by 1, just for good practice.