Search code examples
sqldbisam

SQL Subtract Min And Max

I'm simply trying to use min() and max() functions in an expression like so:

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

But this doesn't appear to be valid SQL. I keep getting told I can't use aggregate functions in an expression. Could I get some help?

Solution

  • I threw the question onto the DBISAM forum at Elevate (not sure why I didn't do that to begin with), and this is what they came up with:

    Use two passes and create a memory table.

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

    Thanks for all of the help guys.