SQL: Median function for SQL Server

I have been using a Transact-SQL query (see Method 1 – Update entire table below) to get the Median “Order Value” for an entire list of Customers. However, due to the quantity of data, it was taking for ever.

 

Therefore, this morning I decided to write a SQL Server User Defined Function (see Method 2 – Function below) 1 to update the Median Order Value for a single Customer. I then used a UPDATE query to update all of the customers. This took a lot less time to process 🙂

 

Method 1 – Update entire table

 

Method 2 – Function

 

For more information regarding “Calculating Mean, Median and Mode with SQL Server”, please see the “Less Than Dot” blog, using the link below:

http://blogs.lessthandot.com/index.php/DataMgmt/DataDesign/calculating-mean-median-and-mode-with-sq

  1. Please ensure you test any script taken from my website on a test/development machine, before running on a production server.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

CJayMead's SQL Server, Endeca, MS Office Help © 2018