CODEWITHSUNDEEP
sqlcaseaverage
Contoweb
Contoweb

Reputation: 47

SQL AVG with Case

I've a DB which stores a value from C to AAA, while C is the worst and AAA the best.

Now I need the average of this value and I don't know how to first convert the values into an int, calculate the average, round the average to an int and convert it back.

Definitions:

C = 1
B = 2
A = 3
AA = 4
AAA = 5

Is that even possible with an SQL statement? I tried to combine AVG and CASE, but I don't bring it to work... Thanks for your help!

Regards,

Upvotes: 2

Views: 7410

Answers (2)

DnL
DnL

Reputation: 141

I've created this example for u. u can cast ur ranking into temp table, then calculate and when ur done, drop it.

create table sof (id int identity,a nvarchar (10)) 
insert into sof values ('a')
insert into sof values ('b')
insert into sof values ('c')
select case a when 'AAA ' then 5
             when 'AA' then 4
             when 'A' then 3
             when 'B' then 2
             else 1
      end as av
into #temp
from sof
----for rounded
select ROUND(AVG(CAST(av AS FLOAT)), 4)
from #temp
--not rounded
select AVG (av)
from #temp

Upvotes: 0

user330315
user330315

Reputation: 

select avg(case score 
             when 'C' then 1 
             when 'B' then 2
             when 'A' then 3
             when 'AA' then 4
             when 'AAA' then 5 
           end) as avg_score
from the_table;

(this assumes that the column is called score)

To convert this back into the "character value", wrap the output in another case:

select case cast(avg_score as int)
         when 1 then 'C'
         when 2 then 'B'
         when 3 then 'A'
         when 4 then 'AA'
         when 5 then 'AAA'
       end as avg_score_value
from (
    select avg(case score 
                 when 'C' then 1 
                 when 'B' then 2
                 when 'A' then 3
                 when 'AA' then 4
                 when 'AAA' then 5 
               end) as avg_score
    from the_table;
) t

The above cast(avg_score as int) assumes ANSI SQL. Your DBMS might have different ways to cast a value to an integer.

Upvotes: 4

Related Questions