CODEWITHSUNDEEP
sql
TheAppleIsRed
TheAppleIsRed

Reputation:

Subtracting minimum value from all values in a column

Is there a another way to subtract the smallest value from all the values of a column, effectively offset the values?

The only way I have found becomes horribly complicated for more complex queries.

CREATE TABLE offsettest(value NUMBER);
INSERT INTO offsettest VALUES(100);
INSERT INTO offsettest VALUES(200);
INSERT INTO offsettest VALUES(300);
INSERT INTO offsettest VALUES(400);

SELECT value - (SELECT MIN(value) FROM offsettest) FROM offsettest;

DROP TABLE offsettest;

I'd like to limit it to a single query (no stored procedures, variables, etc) if possible and standard SQL is preferred (although I am using Oracle).

Upvotes: 3

Views: 3781

Answers (2)

cdonner
cdonner

Reputation: 37688

It would have helped you see your actual query, though, since depending on whether you need to manipulate more than one column this way, and the various minimums come from different rows, there may be more efficient ways to do it. If the OVER() works for you, then fine.

Upvotes: -1

lins314159
lins314159

Reputation: 2520

I believe this works as of ANSI 1999.

SELECT value - MIN(value) OVER() FROM offsettest;

Upvotes: 10

Related Questions