3

I've tried the below query, but the median value shown is wrong.

Query

select 
(select cast(Avg(TotAvg * 1.0 )as decimal(6,2))
 from (values (convert(decimal(6,2), a)),(convert(decimal(6,2), b))
             ,(convert(decimal(6,2), c)),(convert(decimal(6,2), d))
             ,(convert(decimal(6,2), e)),(convert(decimal(6,2), f))
             ,(convert(decimal(6,2), g)),(convert(decimal(6,2), h))
             ,(convert(decimal(6,2), i)),(convert(decimal(6,2), j))
             ,(convert(decimal(6,2), k)),(convert(decimal(6,2), l))
             ,(convert(decimal(6,2), m)),(convert(decimal(6,2), n))
             ,(convert(decimal(6,2), o)),(convert(decimal(6,2), p))
             ,(convert(decimal(6,2), q)),(convert(decimal(6,2), r))
             ,(convert(decimal(6,2), s)),(convert(decimal(6,2), t))
             ,(convert(decimal(6,2), u))
    ) as Totalavg(TotAvg)
) as Median
from temp

Column values are a to u in a row.

Table Values

First row - 1,5,6,7,8,2,6,3,4,5,2,1,6,5,7,8,2,7,6,2,8
Second row - 1,5,6,7,8,2,2,3,2,4,2,1,4,5,9,8,2,7,6,2,8

Create table script

create table temp 
(
    id int identity(1,1),
    a decimal(6,2),
    b decimal(6,2),
    c decimal(6,2),
    d decimal(6,2),
    e decimal(6,2),
    f decimal(6,2),
    g decimal(6,2),
    h decimal(6,2),
    i decimal(6,2),
    j decimal(6,2),
    k decimal(6,2),
    l decimal(6,2),
    m decimal(6,2),
    n decimal(6,2),
    o decimal(6,2),
    p decimal(6,2),
    q decimal(6,2),
    r decimal(6,2),
    s decimal(6,2),
    t decimal(6,2),
    u decimal(6,2)
)

Insert Table Script

insert into temp
    (a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u)
values
    (1,5,6,7,8,2,6,3,4,5,2,1,6,5,7,8,2,7,6,2,8)

insert into temp
    (a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u)
values
    (1,5,6,7,8,2,2,3,2,4,2,1,4,5,9,8,2,7,6,2,8)
7

First step is to unpivot columns into rows. Then you can enumerate the values using row_number and pick the middlest value from there:

select id, avg(val)
from ( 
    select id, val
         , count(*) over (partition by id) as c
         , row_number() over (partition by id order by val) as rn
    from temp unpivot (
             val for col in (a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u)
         ) as x 
) as y
where rn IN ((c + 1)/2, (c + 2)/2) 
group by id;
| improve this answer | |
  • this is working fine in sql server 2014. But i'm trying to execute this query in sql server 2008 r2 getting following error message Incorrect syntax near the keyword 'for'. – Isai Selvan May 7 '18 at 7:56
  • 2
    I have no idea, you should probably ask another question. Make sure you include all relevant info such as error messages and how to reproduce the error. – Lennart May 18 '18 at 13:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.