CODEWITHSUNDEEP
sqlsql-serveroracle-database
Inquisitive
Inquisitive

Reputation: 7836

Query to represent percentages in terms of min and max

I have a column that contain some values:

Values
 10
 20
 30
 40
 50

I need a query that would basically give me two columns like this :

Values  PercentageValues
10        0
20        x
30        y
40        z
50       100

The way the values are calculated is this : 10(min) is considered 0 % and 50(max) is considered is considered to be 100%. So x which is a difference of 10 from min will be:

x= 100/(50-10) * 10 = 25

P.S: Ideally I would like to have a query that can run in both Oracle and SQL Server.

Upvotes: 1

Views: 1184

Answers (2)

Andomar
Andomar

Reputation: 238078

select  100.0 / (maxval - minval) * ([Values] - minval)
from    (
        select  *
        ,       min([Values]) over () as minval
        ,       max([Values]) over () as maxval
        from    Table1
        ) SubQuerALias

Example at SQL Fiddle.

Upvotes: -1

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You can do this using window functions:

select t.value,
       (value - minvalue) * 100.0/(maxvalue - minvalue) 
from (select t.*,
             min(value) over () as minvalue,
             max(value) over () as maxvalue
      from t
     ) t

You can do the same thing with an explicit join:

select t.value,
       (value - minvalue) * 100.0/(maxvalue - minvalue) 
from t cross join
     (select min(value) as minvalue, max(value) as maxvalue
      from t
     ) const

Upvotes: 3

Related Questions