Welcome to another article in our series of SQL Server query challenges! In this article, we will explore how to calculate median values in SQL Server and use them to analyze sales data for a classic car retailer.

Imagine you are working for Prestige Cars Ltd, a small classic car retailer. The CEO has requested a report that shows the difference between the median discount and the actual discount for all sales per customer. Your task is to come up with the SQL query to fulfill this request.

Here is one possible solution:

SELECT DISTINCT
    CU.CustomerName,
    TotalSalePrice,
    TotalSalePrice - PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY TotalSalePrice) OVER (PARTITION BY CustomerName) AS SaleToMedianDelta
FROM
    Data.Sales AS SA
INNER JOIN
    Data.Customer CU ON SA.CustomerID = CU.CustomerID

This query will give you the desired results, displaying the median values with the help of the PERCENTILE_CONT() function.

So, how does it work?

SQL Server does not have a built-in median function like it does for average (AVG()). However, you can calculate the median using the PERCENTILE_CONT() function. To find the median value, you need to use the following SQL elements:

• PERCENTILE_CONT(0.5): This function finds the median value in a dataset. By specifying 0.5 inside the parentheses, you indicate that you are looking for the median.
• WITHIN GROUP: This allows you to specify the numeric field from which you want to extract the median value. You also need to include the ORDER BY clause.
• OVER(PARTITION BY): Similar to other windowing functions, PERCENTILE_CONT() lets you segment the dataset into subgroups. In this case, you can find the median value for each customer by specifying the CustomerName in the PARTITION BY clause.

In the provided example, the query joins the Sales and Customer tables since they contain the necessary fields. Once the CustomerName and SalePrice fields are selected, the query extracts the median value per customer and subtracts it from the sale price for each vehicle sold.

Calculating median values in SQL Server can be a useful tool for statistical analysis. It allows you to compare sales prices to the median, providing insights into the discount offered to each customer.

That’s it for this article! We hope you found this example helpful in expanding your SQL Server querying skills. Stay tuned for more articles in this series.

This article is adapted from the book “Query Answers with SQL Server Volume II: In-Depth Querying” by Adam and Karine Aspin. Available as both a paperback and an eBook from all good resellers including Amazon, Kobo, and iBooks, as well as all good bookstores.

The sample data is available either in the accompanying material for this article or on the Tetras Publishing website at: www.tetraspublishing.com/sqlserver.

The Series: This article is part of a series of SQL Server query challenges. You can find all the articles on the Query Answers page.