How to find median value for a row in SQL server 2008

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

I'm trying to find the median value in a row in sql server. but i couldn't get it any answer. below query i'm tried.

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), s)),(convert(decimal(6,2), u))) as Totalavg(TotAvg)) 
as Median

from temp

What I have tried:

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), s)),(convert(decimal(6,2), u))) as Totalavg(TotAvg)) 
as Median

from temp

Posted 3-May-18 19:53pm

IsaiSelvan

Updated 3-May-18 20:20pm

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2018-05-03T20:09:00

Solution 1

Perhaps if you didn't have two s values in there:

(convert(decimal(6,2), r)),(convert(decimal(6,2), s)),(convert(decimal(6,2), s)),
                                                  ^                          ^

It might help?

Posted 3-May-18 20:09pm

OriginalGriff

Comments

IsaiSelvan 4-May-18 5:29am

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

OriginalGriff 4-May-18 5:34am

What do you get when you run the query?
What did you expect to get?
What does the data input look like?

IsaiSelvan 4-May-18 5:37am

Am already tried that format. the below is the sample data with 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

I changed you mentioned column. help me

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

OriginalGriff 4-May-18 5:48am

And? What happens when you run it, and what did you expect to happen?

IsaiSelvan 4-May-18 5:54am

Yes i want to get median for each row.

First Row - 1,5,6,7,8,2,6,3,4,5,2,1,6,5,7,8,2,7,6,2,8

Actuval Value - 4.80 (this is wrong)
Expected value - 5 (Correct Output)

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

Actual Value - 4.47 (this is wrong)
Expected Value - 4 (Correct Output)

OriginalGriff 4-May-18 6:08am

Then why are you using AVG? AVG calculates the mean, not the median. The mean of your first row is 4.8095 which is what AVG returns.
To calculate the median is tricker, and depends on which version of SQL you are using: https://sqlperformance.com/2012/08/t-sql-queries/median

IsaiSelvan 4-May-18 6:14am

thank you for your reply. that shows the column based median value finding. But i want the row based median value.

OriginalGriff 4-May-18 6:20am

Of course it does! Nobody stores data the way you are because it is such a pain to work with.
You will have to find the appropriate method for your SQL version, and modify it to work with your column based data.
We can't do everything for you!

IsaiSelvan 4-May-18 6:43am

thank you for your reply.i'm trying to find the solution for this one. But i'm getting wrong value. i've one question for this query. Is it possible to find the median value for row based in a table?.

OriginalGriff 4-May-18 6:46am

Yes - but it's not going to be a "off the shelf" solution because it's not something we normally do.
So you will have to write your own, based on the info you have.

IsaiSelvan 7-May-18 2:56am

Kindly help on this.Because last few days am trying but no result. But, without using pivot and unpivot functionality.

OriginalGriff 7-May-18 4:15am

I'm not going to write it for you! :laugh:
I'd have to spend an hour setting up test data, then evaluate what result I expect from it manually, then code a query to do it, then test it, ... not going to happen: we don't do you work for you, we aren't a "code to order" service.

IsaiSelvan 7-May-18 4:17am

ok thank you for reply.

Maciej Los · Answer 2 · 2018-05-03T20:21:00

Solution 2

Not an answer, but additional information...

What is the fastest way to calculate the median? - SQLPerformance.com[^]

Posted 3-May-18 20:21pm

Maciej Los

Comments

IsaiSelvan 4-May-18 5:37am

Am already tried that format. the below is the sample data with 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

I changed you mentioned column. help me

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

Maciej Los 4-May-18 5:47am

And you want to get median for each row?
What's your issue?

IsaiSelvan 4-May-18 5:56am

Yes i want to get median for each row.

First Row - 1,5,6,7,8,2,6,3,4,5,2,1,6,5,7,8,2,7,6,2,8

Actuval Value - 4.80 (this is wrong)
Expected value - 5 (Correct Output)

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

Actual Value - 4.47 (this is wrong)
Expected Value - 4 (Correct Output)

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)