Database

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

05-03-2018
02:31 AM

05-03-2018
02:31 AM

Hello,

I have a table in the below format:

ID Value 1 Value 2 Value 3 Value 4

1 | 5 | 2 | 4 | 1 |

2 | 10 | 12 | 4 | 0 |

3 | 6 | 2 | 9 | 1 |

4 | 15 | 22 | 40 | 7 |

5 | 5 | 9 | 4 | 2 |

How can I use the median function on more than 1 column?

For e.g., median(value1, value2, value3, value4) wont work.

Solved! Go to Solution.

Accepted Solutions

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

05-03-2018
10:25 PM

05-03-2018
10:25 PM

-- Cross Joined for normalization CREATE TABLE OneToFour(i INT); INSERT INTO OneToFour(1); INSERT INTO OneToFour(2); INSERT INTO OneToFour(3); INSERT INTO OneToFour(4); SELECT id, Avg(val) FROM ( SELECT id ,CASE i -- normalize columns to rows WHEN 1 THEN c1 WHEN 2 THEN c2 WHEN 3 THEN c3 WHEN 4 THEN c4 END AS val FROM vt CROSS JOIN OneToFour QUALIFY Row_Number() Over (PARTITION BY id ORDER BY val) BETWEEN 2 AND 3 ) AS dt GROUP BY id

But for exactly four columns you can do a simple:

*Average all values minus the least and the greatest*

(c1 +c2 +c3 +c4 -Greatest(c1,c2,c3,c4) -Least(c1,c2,c3,c4))/2.0

1 ACCEPTED SOLUTION

7 REPLIES 7

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

05-03-2018
11:13 AM

05-03-2018
11:13 AM

Re: Need help on using Median on more than one column

Which result do you expect for that calculation?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

05-03-2018
11:26 AM

05-03-2018
11:26 AM

Re: Need help on using Median on more than one column

Median function works like below,

If we consider row 1, there are four values in it 1 2 4 5 (ordered ascending).

Median would be 2 + 4= 6/2=3

The existing median function works for a single column at a time just like any other aggregate function. I want to find out a way, median can be calculated across 4 columns at a time for each ID.

Least and Greatest can be used instead of Min and Max in such cases. I want a similar result but for median.

Thanks

If we consider row 1, there are four values in it 1 2 4 5 (ordered ascending).

Median would be 2 + 4= 6/2=3

The existing median function works for a single column at a time just like any other aggregate function. I want to find out a way, median can be calculated across 4 columns at a time for each ID.

Least and Greatest can be used instead of Min and Max in such cases. I want a similar result but for median.

Thanks

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

05-03-2018
01:29 PM

05-03-2018
01:29 PM

Re: Need help on using Median on more than one column

Well, Median is an aggregate function, if you want to calculate it on a denormalized table you should: #1 normalize it, #2 apply a ROW_NUMBER and then #3 average the values of rownumber 2&3.

Btw, is the calculation actually based on **4** columns?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

05-03-2018
06:39 PM

05-03-2018
06:39 PM

Re: Need help on using Median on more than one column

Thanks ..But can u elaborate a little more..With an example maybe .?

Yes the calculation is based only on 4 columns

Yes the calculation is based only on 4 columns

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

05-03-2018
06:40 PM

05-03-2018
06:40 PM

Re: Need help on using Median on more than one column

Row number is row wise right? I need the average of the 2 sorted column values..

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

05-03-2018
10:25 PM

05-03-2018
10:25 PM

-- Cross Joined for normalization CREATE TABLE OneToFour(i INT); INSERT INTO OneToFour(1); INSERT INTO OneToFour(2); INSERT INTO OneToFour(3); INSERT INTO OneToFour(4); SELECT id, Avg(val) FROM ( SELECT id ,CASE i -- normalize columns to rows WHEN 1 THEN c1 WHEN 2 THEN c2 WHEN 3 THEN c3 WHEN 4 THEN c4 END AS val FROM vt CROSS JOIN OneToFour QUALIFY Row_Number() Over (PARTITION BY id ORDER BY val) BETWEEN 2 AND 3 ) AS dt GROUP BY id

But for exactly four columns you can do a simple:

*Average all values minus the least and the greatest*

(c1 +c2 +c3 +c4 -Greatest(c1,c2,c3,c4) -Least(c1,c2,c3,c4))/2.0

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

05-04-2018
01:59 AM

05-04-2018
01:59 AM

Re: Need help on using Median on more than one column

Awesome ! Thanks a lot Dieter :)

Such a simple yet effective way...Now I have started to scratch my head and wonder why couldnt I think of it...

I am new to the group..I guess I am gonna bug u guys a lot more ;)