SQL Median

How do you calculate the median of a dataset in SQL?

The median is the middle value in a sorted dataset. SQL doesn't have a direct median function. We need to use other functions like `PERCENTILE_CONT` or a combination of `ORDER BY` and `ROW_NUMBER` to calculate it.

Thank you! Your submission has been received!

Oops! Something went wrong while submitting the form.

Description

Calculating the median in SQL requires a bit more work than using a built-in function. Unlike some other aggregate functions like `AVG` or `SUM`, SQL doesn't directly provide a `MEDIAN` function. This means we need to find a way to determine the middle value in a sorted dataset. One common approach is to use the `PERCENTILE_CONT` function, which returns the value at a specific percentile. To find the median, we use the 50th percentile. Alternatively, we can use a combination of `ORDER BY` and `ROW_NUMBER` to rank the data and then identify the middle value. This method is more flexible, but requires more code.

Why SQL Median is important

Understanding how to calculate the median is crucial for data analysis. The median provides a robust measure of central tendency, less susceptible to outliers than the mean. This is vital for understanding the typical value in a dataset, especially when dealing with skewed distributions.

SQL Median Example Usage


-- Sample tables
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50)
);

CREATE TABLE Orders (
    OrderID INT PRIMARY KEY,
    CustomerID INT,
    OrderDate DATE,
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

-- Insert sample data
INSERT INTO Customers (CustomerID, FirstName, LastName) VALUES
(1, 'John', 'Doe'),
(2, 'Jane', 'Smith'),
(3, 'Peter', 'Jones');

INSERT INTO Orders (OrderID, CustomerID, OrderDate) VALUES
(101, 1, '2023-10-26'),
(102, 2, '2023-10-27');

-- Perform LEFT JOIN
SELECT
    c.CustomerID,
    c.FirstName,
    c.LastName,
    o.OrderID,
    o.OrderDate
FROM
    Customers c
LEFT JOIN
    Orders o ON c.CustomerID = o.CustomerID;

SQL Median Syntax

Common Mistakes

Forgetting to sort the data using `ORDER BY` when using `PERCENTILE_CONT` or `ROW_NUMBER`.
Incorrectly calculating the row number for the median (e.g., using `COUNT(*)` instead of `COUNT(*) / 2 + 1` for odd datasets).
Assuming SQL has a built-in `MEDIAN` function.
Not handling cases where the dataset has an even number of rows.

SQL Median

How do you calculate the median of a dataset in SQL?

Description

Why SQL Median is important

SQL Median Example Usage

SQL Median Syntax

Common Mistakes

Frequently Asked Questions (FAQs)

Want to learn about other SQL terms?

dbt tests

dbt utils

dbt Tutorial Glossary: Build, Test & Deploy SQL Models