I want to do something like this:

SELECT WSN, MAX(BASE) - MIN(TOP)FROM PERFS GROUP BY WSN

But I keep getting an error stating "Aggregate functions cannot be used within an expression"

Any thoughts?

You might have to do this in two swoops..as the error indicates, you cannot use aggregate functions within expressions..

SELECT WSN MAX(BASE) as MaxBase, MIN(TOP) as MinTop INTO memory FirstPass FROM PERFS GROUP BY WSN
;
SELECT (MaxBase - MinTop) as MyCalc FROM memory FirstPass

Note, you may need to use memory\Firstpass depending on your version of DBISAM.

John

jframe wrote:

I want to do something like this:

SELECT WSN, MAX(BASE) - MIN(TOP)FROM PERFS GROUP BY WSN

But I keep getting an error stating "Aggregate functions cannot be used within an expression"

Any thoughts?

This works. Thanks!

John Easley wrote:

You might have to do this in two swoops..as the error indicates, you cannot use aggregate functions within expressions..

SELECT WSN MAX(BASE) as MaxBase, MIN(TOP) as MinTop INTO memory FirstPass FROM PERFS GROUP BY WSN
;
SELECT (MaxBase - MinTop) as MyCalc FROM memory FirstPass

Note, you may need to use memory\Firstpass depending on your version of DBISAM.

John

jframe wrote:

I want to do something like this:

SELECT WSN, MAX(BASE) - MIN(TOP)FROM PERFS GROUP BY WSN

But I keep getting an error stating "Aggregate functions cannot be used within an expression"

Any thoughts?

<< I want to do something like this:

SELECT WSN, MAX(BASE) - MIN(TOP)FROM PERFS GROUP BY WSN

But I keep getting an error stating "Aggregate functions cannot be used
within an expression" >>

Is this DBISAM 4.x, or earlier ?  You should be able to use aggregate
functions in expressions in later 4.x versions.

Tim Young
Elevate Software
www.elevatesoft.com