Display Median Values

Question

Display Median Values

Adam Aspin

SSCarpal Tunnel

Points: 4890
More actions
June 30, 2018 at 4:05 am

#356629

Comments posted to this topic are about the item Display Median Values

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Parker Smith SSChasing Mays Points: 619 More actions · Answer 1

I like the technique, and learning about a function that was new to me, but the article is confusing in a couple of ways.
The narrative talks about discount, but the query code refers to 'TotalSalePrice'.
The 'TotalSalePrice' column is not in the diagram.
I still got the point, but these were slightly distracting.

Ehren Battermann Old Hand Points: 358 More actions · Answer 2

Thanks for the article. I found this helpful to expand my knowledge of functionality in more recent versions of SQL Server.
One thing that might be worth pointing out directly in your article is that the results are interpolated, to help draw attention to the fact that the result may not reflect a specific data point already defined within the set. While documentation for PERCENTILE_CONT does point this out, and it might be intuitive from some of the details in the way you approach the topic in your article, this is an important distinction from a conceptual/business/mathematical perspective versus the classic definition of a median.

zapallina Valued Member Points: 56 More actions · Answer 3

Hi Adam,

Thanks for sharing this, I found it really cool indeed. I have been asked about calculating the median in the past and there is always that juggling of code because of the version of the engine. What I still do is, once the window function is created, I test it against another way to obtain the same results e.g. temp tables and ctes combined, etc. just to make sure of the impact of index and cpu consumption is better using either technique.

Thanks!

MMartin1 One Orange Chip Points: 27774 More actions · Answer 4

NTILE be incorporated in all versions to give a median, but the code is lengthy and not as clean as presented here.

----------------------------------------------------