SQL Count Unique

Galaxy Glossary

How do you count unique values in a SQL table?

The `COUNT(DISTINCT column)` function in SQL is used to count the unique values within a specified column. It's crucial for getting a precise count of distinct items, avoiding duplicates. This is essential for tasks like finding the number of unique customers or products.

Thank you! Your submission has been received!

Oops! Something went wrong while submitting the form.

Description

The `COUNT(DISTINCT column)` function in SQL is a powerful tool for counting unique values in a specific column of a table. Unlike the standard `COUNT()` function, which counts all rows, `COUNT(DISTINCT column)` only counts each unique value once. This is particularly useful when you need to know the number of distinct items, such as the number of unique product names, customer IDs, or order numbers. It's a fundamental aggregate function used in various database queries. For example, in an e-commerce database, you might want to know the total number of unique products sold. Using `COUNT(DISTINCT product_name)` would give you that precise count. This function is also crucial for data analysis, reporting, and business intelligence tasks. It helps in understanding the variety and breadth of data within a dataset.

Why SQL Count Unique is important

The `COUNT(DISTINCT)` function is vital for accurate data analysis. It allows for precise counts of unique items, which is essential for understanding the variety and breadth of data. This is crucial for reporting, business intelligence, and decision-making.

Example Usage


-- Creating a table
CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    City VARCHAR(50)
);

-- Inserting data into the table
INSERT INTO Customers (CustomerID, FirstName, LastName, City)
VALUES
(1, 'John', 'Doe', 'New York'),
(2, 'Jane', 'Smith', 'Los Angeles');

-- Retrieving data from the table
SELECT * FROM Customers;

-- Updating data in the table
UPDATE Customers
SET City = 'Chicago'
WHERE CustomerID = 2;

-- Deleting data from the table
DELETE FROM Customers
WHERE CustomerID = 1;

Common Mistakes

Forgetting to use `DISTINCT` when you need a unique count, leading to incorrect results.
Using `COUNT(*)` when you need a unique count, which will count all rows, including duplicates.
Incorrectly specifying the column for `COUNT(DISTINCT)` which will result in an error or an incorrect count.

SQL Count Unique

How do you count unique values in a SQL table?

Description

Why SQL Count Unique is important

Example Usage

Common Mistakes

Want to learn about other SQL terms?

With Cte In SQL

Where Vs Having SQL

Windows Functions In SQL