Using derived field in aggregate Select

[donjohnson]

Hello!
I have a problem, and hope it is my stupidity instead of a "can't be done" problem.

My data rows have (subset):
Location (PK fld1)
Shift (PK fld2)
Program (PK fld3)
List (PK fld4)
Goal
Hours
Sales

What I need to do is in a query, produce a total of hours and sales per Program per Location. That part is easy. I also need to have an average Goal (easy), but then I need a percent of goal. This is calculated as:
((sales/hours)/goal)

I don't want to store a calculated field in the table, but cannot find a way to roll everything up at the location level, since the goal is different for each list, and there can be multiple lists per program.

If I could derive a field call GoalHours for each list using this:
((sales/hours)/goal)*hours
I could then sum the GoalHours and divide by sum(Hours) to get what I need.

However, I was not able to use this calculation, since I was not using an aggregate function for Goal.

Can anyone help me?

Don Johnson

 

[PHV]

Am I missing something ?
Sum(Sales/Goal)/Sum(Hours)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[donjohnson]

Okay - my mistake here. I was getting a div/0 error, and so tried to use a case statement, and that is what gave me my first error about a non-aggregate field.

Consequently, I will add a WHERE clause to only take records with a goal > 0, and then use a UNION ALL to get the rest of the records, and defaulting the PCTGOAL field to 0 to avoid the div/0 error.

Thanks for the help - sometimes it just takes a second pair of eyes and a good night's sleep to see clearly!

Don