SQL Subtract Min And Max

Question

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?

which database is this for? Something like that would work perfectly fine in MySQL. — Marc B, Feb 24 '14 at 16:17
@AlexK. I'm thinking that if `TOP` was a reserved word in the poster's database, it wouldn't have let them make a table with `TOP` as a column name. — Mr. Llama, Feb 24 '14 at 16:33

Patrick Hofman · Answer 1 · 2014-02-24T16:38:50.620

5

Without the database platform we can't tell that much, but try this:

select wsn, top - base
from
( SELECT WSN
  ,      MIN(TOP) top
  ,      MAX(BASE) base
  FROM   PERFS
  GROUP
  BY     WSN
)

I think the actual answer can be found in the documentation where you can find that TOP is a keyword indeed and tables are escaped with ".

Try this:

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

edited Feb 24 '14 at 16:38

answered Feb 24 '14 at 16:17

Patrick Hofman

153,850
22
249
325

The edited version still produces "Aggregate functions cannot be used within expressions". This is maddening. What I want to do is so simple. – John Feb 24 '14 at 16:41
@John: you mean the last example? – Patrick Hofman Feb 24 '14 at 16:42
Yep, the last example. – John Feb 24 '14 at 16:43
And if you use `[TOP]`? – Patrick Hofman Feb 24 '14 at 16:43
Produces the same result. – John Feb 24 '14 at 16:46
If you replace `min(top)` by `min(base)`, does it work? (I know it has an invalid end result, but just to try) – Patrick Hofman Feb 24 '14 at 16:47
If you remove the subtraction and select just one of the two fields? – Patrick Hofman Feb 24 '14 at 16:58
Works with just selection. – John Feb 26 '14 at 16:28

score 2 · Accepted Answer · answered Feb 26 '14 at 16:33

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.

score 1 · Answer 3 · answered Feb 24 '14 at 16:18

The best answer might depend on what type of SQL you're using, as a common table expression would come in really handy for this. I assume TOP and BASE are fields in the PERFS table?

Here's my best guess:

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

Edit: Patrick's answer would probably be better.

score 0 · Answer 4 · edited Feb 24 '14 at 16:41

0

This works for me using Oracle. What DBMS are you using?

The statement below creates a sample inline table that you can query against. I provided example code here that can be run as-is (self contained). You have to adapt it to match your own database schema:

WITH TEST_DATA AS (
  SELECT 'ted' AS name, 1 as val FROM DUAL UNION ALL 
  SELECT 'ted' AS name, 2 as val FROM DUAL UNION ALL 
  SELECT 'ted' AS name, 3 as val FROM DUAL UNION ALL 
  SELECT 'sam' AS name, 5 as val FROM DUAL UNION ALL 
  SELECT 'sam' AS name, 10 as val FROM DUAL
)SELECT name, MIN(val) - MAX(val) as minmax
 FROM TEST_DATA
GROUP BY name

edited Feb 24 '14 at 16:41

Patrick Hofman

153,850
22
249
325

answered Feb 24 '14 at 16:22

Michael Ford

851
6
9

1

It would be more useful to OP if you use the sample in the question. – Patrick Hofman Feb 24 '14 at 16:25
Thanks for the feeback, Patrick. I am not sure that is worth a mark down, but there you go. – Michael Ford Feb 24 '14 at 16:31
I had trouble to catch the meaning of your answer, because it had all the `union`s, and I am pretty good in SQL. I think the OP doesn't understand what you are doing... – Patrick Hofman Feb 24 '14 at 16:34
Doing that creates a sample inline table that you can query against. I provided example code here that can be run as-is (self contained). – Michael Ford Feb 24 '14 at 16:39
I know, but does OP know that? – Patrick Hofman Feb 24 '14 at 16:40

score 0 · Answer 5 · answered Aug 15 '19 at 17:14

0

select MAX(population) - MIN(population) as value from city;

where the query is to find the max - min from population in the city table

value- is the variable name where it can be changed to any

answered Aug 15 '19 at 17:14

Neo_17

1

SQL Subtract Min And Max

5 Answers5