SQL Server : create new column category price according to price column

Question

I have a SQL Server table with a column price looking like this:

10
96
64
38
32
103
74
32
67
103
55
28
30
110
79
91
16
71
36
106
89
87
59
41
56
89
68
32
80
47
45
77
64
93
17
88
13
19
83
12
76
99
104
65
83
95

Now my aim is to create a new column giving a category from 1 to 10 to each of those values.

For instance the max value in my column is 110 the min is 10. Max-min = 100. Then if I want to have 10 categories I do 100/10= 10. Therefore here are the ranges:

10-20    1
21-30    2
31-40    3
41-50    4
51-60    5
61-70    6
71-80    7
81-90    8
91-100   9
101-110 10

Desired output:

my new column called cat should look like this:

price        cat
-----------------
10           1
96           9
64           6
38           3
32           3
103         10
74           7
32           3
67           6
103         10
55           5
28           2
30           3
110         10
79           7
91           9
16           1
71           7
36           3
106         10
89           8
87           8
59           5
41           4
56           5
89           8
68           6
32           3
80           7
47           4
45           4
77           7
64           6
93           9
17           1
88           8
13           1
19           1
83           8
12           1
76           7
99           9
104         10
65           6
83           8
95           9

Is there a way to perform this with T-SQL? Sorry if this question is maybe too easy. I searched long time on the web. So either the problem is not as simple as I imagine. Either I entered the wrong keywords.

Answer 1

Yes, almost exactly as you describe the calculation:

select price,
       1 + (price - min_price) * 10 / (max_price - min_price + 1) as decile
from (select price, 
             min(price) over () as min_price,
             max(price) over () as max_price
      from t
     ) t;

The 1 + is because you want the values from 1 to 10, rather than 0 to 9.

Answer 2

Yes - a case statement can do that.

select 
    price
    ,case 
        when price between 10  and 20   then 1
        when price between 21  and 30   then 2
        when price between 31  and 40   then 3
        when price between 41  and 50   then 4
        when price between 51  and 60   then 5
        when price between 61  and 70   then 6
        when price between 71  and 80   then 7
        when price between 81  and 90   then 8
        when price between 91  and 100  then 9
        when price between 101 and 110  then 10
        else null
    end as cat
from [<enter your table name here>]