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

Question

0.00/5 (No votes)

See more:

The below table have a record. How can i find the median value for the below table.

6.8	6.82	6.8	6.71	6.65	6.7	6.7	6.85	6.6	6.77	6.84	6.84	6.8	0	6.58	6.67	6.83	6.75	6.75	0	6.7

What I have tried:

The below table have a record. How can i find the median value for the below table.

<pre>6.8	6.82	6.8	6.71	6.65	6.7	6.7	6.85	6.6	6.77	6.84	6.84	6.8	0	6.58	6.67	6.83	6.75	6.75	0	6.7

Query:
======

select STUFF((ISNULL(','+ convert(varchar(10),s.a),'') + ISNULL(','+ convert(varchar(10),s.b),'') + 
			ISNULL(','+ convert(varchar(10),s.c),'') + ISNULL(','+ convert(varchar(10),s.c1),'') + 
			ISNULL(','+ convert(varchar(10),s.d),'') + ISNULL(','+ convert(varchar(10),s.e),'') +
			ISNULL(','+ convert(varchar(10),s.f),'') + ISNULL(','+ convert(varchar(10),s.g),'') +
			ISNULL(','+ convert(varchar(10),s.h),'') + ISNULL(','+ convert(varchar(10),s.i),'') +
			ISNULL(','+ convert(varchar(10),s.j),'') + ISNULL(','+ convert(varchar(10),s.k),'') +
			ISNULL(','+ convert(varchar(10),s.l),'') + ISNULL(','+ convert(varchar(10),s.m),'') +
			ISNULL(','+ convert(varchar(10),s.n),'') + ISNULL(','+ convert(varchar(10),s.o),'') +
			ISNULL(','+ convert(varchar(10),s.p),'') + ISNULL(','+ convert(varchar(10),s.q),'') +
			ISNULL(','+ convert(varchar(10),s.r),'') + ISNULL(','+ convert(varchar(10),s.s),'') +
			ISNULL(','+ convert(varchar(10),s.t),'')),1,1,'') as Merge from temptable s

Posted 10-Apr-18 2:45am

IsaiSelvan

Updated 10-Apr-18 3:37am

Add a Solution

Comments

CHill60 10-Apr-18 9:06am

converting the numbers to varchar is not going to help with a mathematical requirement!

IsaiSelvan 10-Apr-18 9:37am

ok thanks. i'm also getting the error message. then how can i find the median value. can you please give me the suggestion

1 solution

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)

CHill60 · Accepted Answer · 2018-04-10T03:38:00

As I intimated in my comment - do not convert numbers to varchars!

There are several different ways of calculating a median in SQL - the one to choose will depend on which version of SQL you are using but you have not shared that information with us.

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

Your first problem is going to be to get the data into a usable, sorted, form... putting all the numbers into one long string is also not going to help that process.
Use something like this

SQL

select xx, ROW_NUMBER() OVER (ORDER BY xx) AS rnk
	FROM (SELECT 1 as ID, a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u FROM #temptable) t
	UNPIVOT
	(xx for zz in (a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u)) as pvt ORDER BY rnk

[EDIT] I have subsequently found this solution Function to Calculate Median in SQL Server - Stack Overflow[^] which equates to

SQL

SELECT AVG(xx)
FROM
(
	select xx, zz, ROW_NUMBER() OVER (ORDER BY xx ASC,zz ASC) AS rnkA, ROW_NUMBER() OVER (ORDER BY xx DESC, zz DESC) AS rnkD
	FROM (SELECT 1 as ID, a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u FROM #temptable) t
	UNPIVOT
	(xx for zz in (a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u)) as pvt
) q
WHERE RnkA IN (RnkD, RnkD - 1, RnkD + 1)

I've used the column name (zz) as the "disambiguator" mentioned.