Combining DISTINCT and COUNT in an SQL Query

1. Introduction

Using DISTINCT and COUNT together in SQL is a common technique to count the number of unique values in one or more columns. This method is often used for reporting categories, users, or identifiers in a database without duplicates.

In this tutorial, we’ll learn how to combine DISTINCT and COUNT in an SQL query. The examples are based on the Baeldung University schema and are compatible with MySQL, PostgreSQL, and SQL Server.

2. Combining DISTINCT and COUNT for a Single Column

Generally, we can combine DISTINCT and COUNT in a straightforward manner to count unique values in a single column:

SELECT COUNT(DISTINCT textbook) FROM Course;
+--------------------------+
| COUNT(DISTINCT textbook) |
+--------------------------+
|                       45 |
+--------------------------+
1 row in set (0.00 sec)

This query counts the unique values in the textbook column from the Course table.

Additionally, we can rename the displayed column name using AS:

SELECT COUNT(DISTINCT textbook) AS unique_textbook_count FROM Course;
+-----------------------+
| unique_textbook_count |
+-----------------------+
|                    45 |
+-----------------------+
1 row in set (0.00 sec)

In this query, the AS keyword provides a temporary alias for the specified column name in the result set.

The query counts the unique values and is case-sensitive. To perform a case-insensitive count for unique values, we can use either the LOWER() or UPPER() function.

3. Combining DISTINCT and COUNT for Multiple Columns

There are different ways to combine DISTINCT and COUNT in an SQL query for multiple columns.

3.1. Direct Approach

In MySQL and SQL Server, we can use a similar syntax with the addition of new columns:

SELECT COUNT(DISTINCT textbook, credits) AS unique_count FROM Course;
+--------------+
| unique_count |
+--------------+
|           55 |
+--------------+
1 row in set (0.00 sec)

MySQL and SQL Server treat this query as counting the number of unique combinations of values across the specified columns, i.e., textbook and credits.

However, PostgreSQL doesn’t support listing multiple columns directly within a COUNT(DISTINCT …) construct. Yet, by enclosing the column names in parentheses, PostgreSQL treats them as a single tuple and applies DISTINCT to the combination.

Let’s see this in action:

SELECT COUNT(DISTINCT(textbook, credits)) AS unique_count FROM Course;

This PostgreSQL query also returns the same result set.

3.1. Using Subquery

To leverage subqueries as the COUNT-DISTINCT link, we first write the subquery (inner query) to retrieve the unique combinations from the textbook and credits columns. Then, in the outer query, we count the number of rows returned by the subquery.

Let’s see an example of the technique:

SELECT COUNT(*) AS unique_count FROM (SELECT DISTINCT textbook, credits FROM Course) temp_course;
+--------------+
| unique_count |
+--------------+
|           55 |
+--------------+
1 row in set (0.00 sec)

This query returns the COUNT of rows returned by the inner query.

3.2. Using the CONCAT Function

The CONCAT function concatenates two or more strings and returns a single concatenated string.

Let’s concatenate the two columns and use COUNT(DISTINCT …) on the result:

SELECT COUNT(DISTINCT CONCAT(textbook, '--', credits)) AS unique_count FROM Course;
+---------------+
| unique_count |
+---------------+
|            55 |
+---------------+
1 row in set (0.01 sec)

The query above concatenates the values from the textbook and credits columns, using the string — as a separator. Thus, the resulting string is treated as a single value for the DISTINCT operation.

Furthermore, we can add filters using the WHERE clause:

SELECT COUNT(DISTINCT CONCAT(textbook, '--', credits)) AS unique_count FROM Course WHERE department_id = 4;
+---------------+
| unique_count |
+---------------+
|            13 |
+---------------+
1 row in set (0.00 sec)

Thus, the query counts the unique combinations of values from the textbook and credits columns in the Course table where the department_id is 4.

Alternatively, we can use the CONCAT_WS function with a separator instead of CONCAT to achieve similar results.

4. Conclusion

In this article, we discussed different ways to combine DISTINCT and COUNT in an SQL query.

To begin with, we used the combination of DISTINCT and COUNT for a single column. Then, we applied a similar approach to multiple columns to retrieve the count of all unique combinations of values from each of them. Next, we used a subquery to get unique rows from multiple columns and then counted those rows in the outer query.

In addition, we included the CONCAT function to combine two columns into a single string before counting the unique entries. Finally, we discussed using the WHERE clause to apply filters.