1

Trying to establish a production percentage based on a couple of values from the same table with the following statement:

SELECT avg(sum([Actual]/
(sum(NULLIF([Team Members],0)/(sum(NULLIF([Actual Min],0)))))))
FROM [Shift Production Log]
WHERE [Date] >= DATEADD(dw, -2, :dvalue) AND
  [Line] = :Lpram

And I'm getting the following error:

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

I should also specify I am doing this through a SCADA system that will place parameters that I bind, thus the "Lpram" and "dvalue". Those work in other statements fine, so it appears my problem is the actual select statement itself. Thank you!

Sample Data:

Wei Lin
  • 3,591
  • 2
  • 20
  • 52
Dave Holt
  • 111
  • 7

1 Answers1

0

You have an Aggr function 

sum([Actual]/
(sum(NULLIF([Team Members],0)/(sum(NULLIF([Actual Min],0))))))

Which will be one figure

Then you try to take Avg of this, but this is not scalar. It's like running

select avg(sum(1))

That wont work

EoinS
  • 5,405
  • 1
  • 19
  • 32