When working in SQL databases, you may encounter such instances where you must find the distinct values from a given table and remove the duplicate values. In most cases, we mainly use the distinct clause to specify the column whose values are what we wish to be unique.
But what happens when you want to ensure that the values from multiple columns are unique and there are no duplicates?
In this tutorial, we will learn how to use the SQL features to select two or more columns and ensure that their values are distinct.
Problem:
Suppose we have a table with multiple columns and we want to count the number of distinct combinations of values across these columns.
For example, let’s consider a table of sales data with the customer_id, product_id, and date columns. We want to count the number of unique combinations of customer_id and product_id.
Count the Distinct Combinations on Multiple Columns in SQL
We can count the number of distinct combinations across multiple columns using the COUNT DISTINCT clause and the CONCAT function in SQL.
The CONCAT function allows us to concatenate two or more values into a single value which we can then use to compare and count.
We can better illustrate this using the following syntax:
FROM table_name;
In this case, column1 and column2 refer to the columns that we wish to concatenate while counting and the table_name refers to the target table’s name.
Let us take a sample table:
id INT PRIMARY KEY,
customer_id INT,
product_id INT,
date DATE
);
INSERT INTO sales VALUES
(1, 100, 1, '2023-05-01'),
(2, 101, 1, '2023-05-02'),
(3, 100, 2, '2023-05-02'),
(4, 102, 3, '2023-05-03'),
(5, 101, 2, '2023-05-03'),
(6, 103, 2, '2023-05-04'),
(7, 100, 3, '2023-05-04'),
(8, 102, 1, '2023-05-05'),
(9, 101, 3, '2023-05-05'),
(10, 103, 1, '2023-05-06');
Resulting Table:
To determine the number of unique combinations of the customer_id and the product_id columns from the previous table, we can use the query as follows:
FROM sales;
In the previous query, we use the distinct clause and the concat function to concatenate the values of the customer_id and the product_id with a hyphen. This should create a single value for each combination as shown in the following:
From here, we can use the count function to count the unique combinations from the resulting table.
Conclusion
We hope that this tutorial helped you. In this post, you discovered how to combine the distinct clause, the concat() function, and the count clause to determine the unique values from multiple SQL table columns.
