Getting the Median In T-SQL and Why

We look at median overall and with T-SQL in What Is Median? Why Use Median?. As you can see, we see one way in which we can obtain the median using T-SQL. We also notice a comparison of average versus median and how this impacts data sets: in the example, the median figure may be 0 while the average figure may be five figures. How can this be?

Consider a few contexts of average versus median:

• 9 people who've eaten 2000 calories one day, versus one person who ate 10,000 calories one day - what would the average and median be?
• 2,100 billionaires (measured in US Dollars) out of 8,000,000,000 people while worldwide median wealth is 7,100 (measured in US Dollars) - what's more accurate for grasping the standard person?
• 1% of students borrowing $200,000+ to get a medical degree or law degree while most students borrow less than $40,000 to get a standard degree - what will the impact of median versus average be in a discussion about student debt?

While outliers can affect average, they seldom have an impact on median. Median's underlying measurement won't be swayed by outliers and we seldom have data sets where all of our data values lie on either tail of the bell curve (like all data sets being left or right-tailed). Because of how we order data in T-SQL, we want to ensure that how we get the median is based on how we organize the data.

This isn't to say that we should always use median. There may be some situations where average or other measurements may be more accurate (or we may want to apply filters appropriately). A client in the marketing field once provided data where we looked at four different measurements - median being one. However, the situation called for us to look at other measurements as well.

One regular point I like to remind my audience, especially as of recent: we can often solve the same problem with a variety of techniques. While we look at one or two ways to solve the problem, these aren't the only ways we can approach this problem. The most appropriate solution to a problem is one in which you can troubleshoot quickly in the future and one in which you understand. Be careful about applying solutions that solve a problem, but introduce new problems in the future.

A great re-occuring example of this is my discussion on alerting best practices. There are a variety of alerts everywhere for everything in today's development environments, but many of these alerts cause more issues than they solve (one of which is creating a distraction). Be careful about "easy" solutions which introduce bigger problems. Know your environment and where to apply the best solution. Of course, all these being written, it is helpful to learn new techniques to solve problems because they may be useful in the future.