Distinct SQL

How do you select only unique values from a column in SQL?

The `DISTINCT` keyword in SQL is used to retrieve only unique rows or values from a table. It's crucial for filtering out duplicate data and getting a concise representation of the data.

Thank you! Your submission has been received!

Oops! Something went wrong while submitting the form.

Description

The `DISTINCT` keyword is a powerful tool in SQL that helps you eliminate duplicate rows or values from your query results. Imagine you have a table of customer orders, and you want to see a list of all the unique products ordered. Without `DISTINCT`, you might get multiple entries for the same product. Using `DISTINCT` ensures that each product appears only once in the result set. This is essential for tasks like generating reports, analyzing data trends, and creating summaries where you only need to see unique items. It's important to understand that `DISTINCT` operates on the entire row if you're selecting multiple columns, not just the specified column. If you want to see unique values from a specific column, you'll use `DISTINCT` with that column's name. For example, if you want to see unique customer names, you'd use `SELECT DISTINCT customer_name FROM customers`. This is a fundamental aspect of data manipulation in SQL, allowing you to focus on unique data points.

Why Distinct SQL is important

The `DISTINCT` keyword is crucial for data analysis and reporting. It helps to avoid redundancy and focus on unique data points, making the results more concise and easier to interpret. It's a fundamental tool for any SQL developer working with data.

Distinct SQL Example Usage


-- Sample table: Orders
CREATE TABLE Orders (
    CustomerID INT,
    OrderID INT,
    OrderValue DECIMAL(10, 2)
);

INSERT INTO Orders (CustomerID, OrderID, OrderValue) VALUES
(1, 1, 10.00),
(1, 2, 20.00),
(2, 3, 30.00),
(2, 4, 40.00),
(2, 5, 50.00);

-- Calculate the average order value per customer
SELECT
    CustomerID,
    OrderValue / (SELECT COUNT(*) FROM Orders WHERE Orders.CustomerID = CustomerID) AS AvgOrderValue
FROM
    Orders
GROUP BY
    CustomerID;

Common Mistakes

Forgetting to use `DISTINCT` when you need unique values, leading to duplicate data in the results.
Using `DISTINCT` with aggregate functions (like `SUM`, `AVG`) without understanding that it will apply the function to each unique row, not to all rows.
Incorrectly assuming `DISTINCT` works on individual columns when selecting multiple columns; it operates on the entire row.

Distinct SQL

How do you select only unique values from a column in SQL?

Description

Why Distinct SQL is important

Distinct SQL Example Usage

Common Mistakes

Want to learn about other SQL terms?

With Cte In SQL

Where Vs Having SQL

Window Function SQL