Finding Median Value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Finding Median Value

Author

Topic

cs01rsw
Starting Member

14 Posts

Posted - 2007-03-12 : 14:09:59

Hi,

I have looked all over the web to try to find some very basic / simple explanations of how to get a median value from a group of records in a table but with no luck

the problem i am having is that all the information i find is always centered around getting a median using every single row in the table. except i have groups of data in the table and want to work out a median for each group. the group is identified by 4 different columns (the 5th column is what i want to get the median on but for each group not the entire table) and i want to produce a resulting table that has 1 row for each group and therefore contains the median value for the group instead of the individual numbers that it currently has. e.g. the current table is like this

column1 column2 column3 column 4 column5(median of this)

value 1 value 2 value 3 value 4 1.2
value 1 value 2 value 3 value 4 1.0
value 1 value 2 value 3 value 4 1.5
value 2 value 3 value 4 value 5 0.2
value 2 value 3 value 4 value 5 0.4
etc...

and i need a query to get the results to show like this

column1 column2 column3 column 4 column5

value 1 value 2 value 3 value 4 1.0
value 2 value 3 value 4 value 5 0.3
etc...

This is driving me crazy and i will be very helpful if anyone can help

the statement i need to add it to is:

select pat_demid, pat_lastname, meas_gendate, meas_id, test_gendate, avg(srtot)as meansrtot, avg(sreff)as meansreff, avg(BFRaw)as BF_Rawmean, avg(BFTGV)as BF_TGVmean, count(srtot)as countsrtot, count(sreff)as countsreff
from bodyparametersf
where (srtot is not null) OR (sreff is not null)
group by pat_demid, pat_lastname, meas_gendate, meas_id, test_gendate

thanks very much

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-12 : 14:41:19

Have you tried this?
http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=72538

cs01rsw
Starting Member

14 Posts

Posted - 2007-03-12 : 15:31:38

Hi, thanks very much for this link.

i have now managed to get it to extract the median only if i put in the group values. When i run the following query it asks me to enter the 4 group values, if i do this it gives me the median for that group which is great but i need to be able to fit it into my original query so that it automatically does it for all the groups. i am half way there so would be greatful of any more help. thanks

this is the code that is getting the median:

SELECT DISTINCT ((SELECT MAX(srtot)
FROM (SELECT TOP 50 PERCENT srtot
FROM dbo_BodyParametersF
WHERE pat_demid = v.pat_demid
AND pat_lastname = v.pat_lastname
AND meas_gendate = v.meas_gendate
AND meas_id = v.meas_id
AND test_gendate = v.test_gendate
ORDER BY srtot) AS H1) +
(SELECT MIN(srtot)
FROM (SELECT TOP 50 PERCENT srtot
FROM dbo_BodyParametersF
WHERE pat_demid = v.pat_demid
AND pat_lastname = v.pat_lastname
AND meas_gendate = v.meas_gendate
AND meas_id = v.meas_id
AND test_gendate = v.test_gendate
ORDER BY srtot DESC) AS H2)) / 2 AS SRtotMedian
FROM dbo_BodyParametersF v

and here is the query that i need to fit it into:

select pat_demid, pat_lastname, meas_gendate, meas_id, test_gendate,
avg(srtot)as meansrtot, avg(sreff)as meansreff, avg(BFRaw)as BF_Rawmean, avg(BFTGV)as BF_TGVmean,
((stdev(srtot)/avg(srtot))*100) as srtotcv, ((stdev(sreff)/avg(sreff))*100) as sreffcv, ((stdev(BFRaw)/avg(BFRaw))*100) as BF_Rawcv, ((stdev(BFTGV)/avg(BFTGV))*100) as BF_TGVcv,
count(srtot)as countsrtot, count(sreff)as countsreff
from bodyparametersf
where (srtot is not null) OR (sreff is not null)
group by pat_demid, pat_lastname, meas_gendate, meas_id, test_gendate

i would like to fit it in so that it is another column outputted from the query with the median values

thanks for any help with this

cs01rsw
Starting Member

14 Posts

Posted - 2007-03-12 : 15:44:13

actually i can ask this an easier way:

why does the following code ask me to input the 4 group values and not just create a table with all of the groups and their median values in them:

SELECT DISTINCT ((SELECT MAX(srtot)
FROM (SELECT TOP 50 PERCENT srtot
FROM dbo_BodyParametersF
WHERE pat_demid = v.pat_demid
AND pat_lastname = v.pat_lastname
AND meas_gendate = v.meas_gendate
AND meas_id = v.meas_id
AND test_gendate = v.test_gendate
ORDER BY srtot) AS H1) +
(SELECT MIN(srtot)
FROM (SELECT TOP 50 PERCENT srtot
FROM dbo_BodyParametersF
WHERE pat_demid = v.pat_demid
AND pat_lastname = v.pat_lastname
AND meas_gendate = v.meas_gendate
AND meas_id = v.meas_id
AND test_gendate = v.test_gendate
ORDER BY srtot DESC) AS H2)) / 2 AS SRtotMedian
FROM dbo_BodyParametersF v

thanks very much for any help

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-12 : 15:56:06

I think this is right, your original query in red, the median subquery in blue

SELECT pat_demid, pat_lastname, meas_gendate, meas_id, test_gendate,
meansrtot, meansreff, BF_Rawmean, BF_TGVmean,
srtotcv, sreffcv, BF_Rawcv, BF_TGVcv, countsrtot, countsreff,
((SELECT MAX(srtot)
FROM (SELECT TOP 50 PERCENT srtot
FROM dbo_BodyParametersF
WHERE pat_demid = v.pat_demid
AND pat_lastname = v.pat_lastname
AND meas_gendate = v.meas_gendate
AND meas_id = v.meas_id
AND test_gendate = v.test_gendate
ORDER BY srtot) AS H1) +
(SELECT MIN(srtot)
FROM (SELECT TOP 50 PERCENT srtot
FROM dbo_BodyParametersF
WHERE pat_demid = v.pat_demid
AND pat_lastname = v.pat_lastname
AND meas_gendate = v.meas_gendate
AND meas_id = v.meas_id
AND test_gendate = v.test_gendate
ORDER BY srtot DESC) AS H2)) / 2 AS SRtotMedian

FROM (select pat_demid, pat_lastname, meas_gendate, meas_id, test_gendate,
avg(srtot)as meansrtot, avg(sreff)as meansreff, avg(BFRaw)as BF_Rawmean, avg(BFTGV)as BF_TGVmean,
((stdev(srtot)/avg(srtot))*100) as srtotcv, ((stdev(sreff)/avg(sreff))*100) as sreffcv, ((stdev(BFRaw)/avg(BFRaw))*100) as BF_Rawcv, ((stdev(BFTGV)/avg(BFTGV))*100) as BF_TGVcv,
count(srtot)as countsrtot, count(sreff)as countsreff
from bodyparametersf
where (srtot is not null) OR (sreff is not null)
group by pat_demid, pat_lastname, meas_gendate, meas_id, test_gendate) AS v

cs01rsw
Starting Member

14 Posts

Posted - 2007-03-12 : 16:08:21

hi thanks for the reply, i really appreciate all your help.

when i run this query a text box appears asking me to first enter v.pat_demid, then another appears and asks me to enter v.pat_lastname, then v.meas_gendate, then v.meas_id, then v.test_gendate

i cant seem to see why it is asking this

thanks very much for your help

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-12 : 16:22:35

So you're doing this in Access, not SQL Server right?
It should still work though. When Access pops up a prompt like that it means it isn't finding the column with the name you're using, so it assumes it is a parameter and prompts you to enter the parameter value. But v.pat_demid and the other columns should be coming from the subquery (the one in red). Did you change the alias of that to something other than v? You need to enter it just like that (with the alias "AS v" after the subquery. If you changed it to another name "AS something_else" then you'd have to replace the v. in front of all the names in the median subquery too.

cs01rsw
Starting Member

14 Posts

Posted - 2007-03-12 : 16:26:54

hi thanks, yes i am using access for now, the sql server is at my office so i am just working on the code and when it works in access i can implement it on the sql server

i havent changed the alias so dont know why this is not working. maybe i shud take it in and try it on the sql server. i basically copied and pasted it as you have it above

thanks for the help ill keep trying and let you know

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-12 : 16:42:05

It should work on Access. Are bodyparametersf and dbo_BodyParametersF two different tables?

cs01rsw
Starting Member

14 Posts

Posted - 2007-03-12 : 16:45:37

my apologies the only change that i made was make them consistent as it should be dbo_BodyParametersF

thanks i really appreciate your help. ms access seems to be very tempremental tonight and throwing some very silly errors so maybe i will try it out on the sql server tomorrow and hopefully it will be ok.

ill do that and let you know.

thanks again, very much appreciated. i was completely lost until your help :)

cs01rsw
Starting Member

14 Posts

Posted - 2007-03-13 : 06:52:28

hi i was wondering if you could see anything wrong with this code, as if i try to run this on its own it asks for the v.pat_demid etc...

SELECT DISTINCT ((SELECT MAX(srtot)
FROM (SELECT TOP 50 PERCENT srtot
FROM dbo_BodyParametersF
WHERE pat_demid = v.pat_demid
AND pat_lastname = v.pat_lastname
AND meas_gendate = v.meas_gendate
AND meas_id = v.meas_id
AND test_gendate = v.test_gendate
ORDER BY srtot) AS H1) +
(SELECT MIN(srtot)
FROM (SELECT TOP 50 PERCENT srtot
FROM dbo_BodyParametersF
WHERE pat_demid = v.pat_demid
AND pat_lastname = v.pat_lastname
AND meas_gendate = v.meas_gendate
AND meas_id = v.meas_id
AND test_gendate = v.test_gendate
ORDER BY srtot DESC) AS H2)) / 2 AS SRtotMedian
FROM dbo_BodyParametersF v

thanks very much

cs01rsw
Starting Member

14 Posts

Posted - 2007-03-13 : 07:10:42

ok something very strange is going on. in the link you sent me on your first post (link: http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=72538)
you solved someones problem with the following code:

SELECT DISTINCT Groupname, ((SELECT MAX(value)
FROM (SELECT TOP 50 PERCENT value
FROM view_avgbyperson_day
WHERE Groupname = v.Groupname
ORDER BY value) AS H1) +
(SELECT MIN(value)
FROM (SELECT TOP 50 PERCENT value
FROM view_avgbyperson_day
WHERE Groupname = v.Groupname
ORDER BY value DESC) AS H2)) / 2 AS Median
FROM view_avgbyperson_day v

Because of the problems i created a table named view_avgbyperson_day with value and groupname columns, then run the exact query you posted and a text box popped up asking me to enter the v.groupname

do you have any ideas what may be going on

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-13 : 13:04:13

Here's a working example of that, this runs fine on SQL Server

create table #Test(
GroupName varchar(20),
value decimal(9,2))
insert #Test
select 'Group 1', 1.2 union all
select 'Group 1', 1.0 union all
select 'Group 1', 1.5 union all
select 'Group 2', 0.2 union all
select 'Group 2', 0.4

SELECT DISTINCT Groupname, ((SELECT MAX(value)
FROM (SELECT TOP 50 PERCENT value
FROM #Test
WHERE Groupname = v.Groupname
ORDER BY value) AS H1) +
(SELECT MIN(value)
FROM (SELECT TOP 50 PERCENT value 
FROM #Test 
WHERE Groupname = v.Groupname
ORDER BY value DESC) AS H2)) / 2 AS Median
FROM #Test v

drop table #Test

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-03-13 : 13:24:05

OK, I figured it out. The problem is that you cannot access the main subquery that I aliased as v from a subquery of a subquery. The difference here from the previous example I referred you to is that there was no main subquery, that was using an actual table or view. So I did that for your data and then it works fine. Here is a full example for your data (I left out all the other calculated columns to keep it simple). Note that I created a view to use in place of the subquery at the end, you'll need to do that too. I assume this would work on Access too, you'll just create a saved query instead of a view.

create table dbo_BodyParametersF(
pat_demid int, 
pat_lastname varchar(20), 
meas_gendate datetime, 
meas_id int, 
test_gendate datetime, 
srtot decimal(9,2))
insert dbo_BodyParametersF 
select 1, 'LastName 1', '1/1/2007', 1, '1/1/2007', 1.2 union all
select 1, 'LastName 1', '1/1/2007', 1, '1/1/2007', 1.0 union all
select 1, 'LastName 1', '1/1/2007', 1, '1/1/2007', 1.5 union all
select 2, 'LastName 2', '1/2/2007', 2, '1/2/2007', 0.2 union all
select 2, 'LastName 2', '1/2/2007', 2, '1/2/2007', 0.4
go
create view view_BodyParametersF 
as
select pat_demid, pat_lastname, meas_gendate, meas_id, test_gendate
from dbo_BodyParametersF
where (srtot is not null)
group by pat_demid, pat_lastname, meas_gendate, meas_id, test_gendate
go

SELECT pat_demid, pat_lastname, meas_gendate, meas_id, test_gendate,
((SELECT MAX(srtot) 
FROM (SELECT TOP 50 PERCENT srtot 
FROM dbo_BodyParametersF 
WHERE pat_demid = v.pat_demid 
AND pat_lastname = v.pat_lastname 
AND meas_gendate = v.meas_gendate 
AND meas_id = v.meas_id 
AND test_gendate = v.test_gendate 
ORDER BY srtot) AS H1) + 
(SELECT MIN(srtot) 
FROM (SELECT TOP 50 PERCENT srtot 
FROM dbo_BodyParametersF 
WHERE pat_demid = v.pat_demid 
AND pat_lastname = v.pat_lastname 
AND meas_gendate = v.meas_gendate 
AND meas_id = v.meas_id 
AND test_gendate = v.test_gendate 
ORDER BY srtot DESC) AS H2)) / 2 AS SRtotMedian
FROM view_BodyParametersF v

cs01rsw
Starting Member

14 Posts

Posted - 2007-03-13 : 15:08:15

hi, thanks very much for your replies. i really appreciate your help, i will find some time later to work through your examples and let you know how i get on.

thanks very much again

cs01rsw
Starting Member

14 Posts

Posted - 2007-03-13 : 19:43:01

hi,

i have just been through your example and see what you have done but for some reason it is still asking me for the v.pat_name etc... just wondering if i have done this right.

what i have done is, in access i cant create a view so i just done the query equivalent to the view:

select pat_demid, pat_lastname, meas_gendate, meas_id, test_gendate
from dbo_BodyParametersF
where (srtot is not null)
group by pat_demid, pat_lastname, meas_gendate, meas_id, test_gendate

and i named this query 2. i then created another query to get the median which is:

SELECT pat_demid, pat_lastname, meas_gendate, meas_id, test_gendate,
((SELECT MAX(srtot)
FROM (SELECT TOP 50 PERCENT srtot
FROM dbo_BodyParametersF
WHERE pat_demid = v.pat_demid
AND pat_lastname = v.pat_lastname
AND meas_gendate = v.meas_gendate
AND meas_id = v.meas_id
AND test_gendate = v.test_gendate
ORDER BY srtot) AS H1) +
(SELECT MIN(srtot)
FROM (SELECT TOP 50 PERCENT srtot
FROM dbo_BodyParametersF
WHERE pat_demid = v.pat_demid
AND pat_lastname = v.pat_lastname
AND meas_gendate = v.meas_gendate
AND meas_id = v.meas_id
AND test_gendate = v.test_gendate
ORDER BY srtot DESC) AS H2)) / 2 AS SRtotMedian
FROM query2 v

you will notice that where you queried the view i have just referenced the query 2 that i created. I have got this right?

i feel bad for continuing to ask for your help but i really need to solve this and i really appreciate all you help :)

cs01rsw
Starting Member

14 Posts

Posted - 2007-03-13 : 19:56:03

i always seem to be getting the problem in the section that works out the median, i cant seem to see why

thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources