Counting Distinct Values With Conditions in SQL

1. Introduction

Counting distinct values in SQL is essential for tasks like identifying unique items, or transactions. However, it’s often necessary to apply conditions, such as filtering results or handling complex queries.

In this article, we’ll walk through how to count distinct values with conditions using SQL. We’ll see how to use the WHERE clause, apply multiple conditions, and count across columns.

2. Understanding DISTINCT in SQL

The DISTINCT keyword in SQL is used to eliminate duplicate values from a query’s result set, ensuring that only unique rows are returned. This is especially helpful when we want to count or retrieve only the unique occurrences of a specific column. Let’s explore the concept of DISTINCT using the University schema.

2.1. Counting Unique Departments

Suppose we want to find out how many unique departments exist in the university. We can use the DISTINCT keyword on the name column of the Department table to achieve this.

SELECT DISTINCT name 
FROM Department;

2.2. Unique Course Codes in a Department

Let’s say we want to find out how many unique courses are offered by each department. For this, we can use DISTINCT on the code column in the Course table to see how many different courses exist in each department:

SELECT DISTINCT code 
FROM Course;

This query returns all unique course codes offered across all departments.

2.3. Distinct Students by Enrollment Year

To see how many students enrolled in the university in different years, we can query the Student table using DISTINCT on the enrollment_date:

SELECT DISTINCT YEAR(enrollment_date) AS enrollment_year 
FROM Student;

This will show the years in which students enrolled, without duplication.

3. Using COUNT and DISTINCT Together

The COUNT function in SQL is used to count the number of rows in a result set. Combined with the DISTINCT keyword, it counts only the unique occurrences of a value in a specific column. This is useful for finding out how many distinct items exist in a dataset without duplicates. Let’s explore how to use COUNT and DISTINCT together using examples based on the University schema.

3.1. Counting Unique Programs

If we want to know how many unique programs are offered by the university, we can use COUNT(DISTINCT) on the name column of the Program table:

SELECT COUNT(DISTINCT name) AS unique_programs
FROM Program;

This query will return the number of distinct programs by name, ignoring any duplicates in the Program table.

3.2. Counting Unique Departments

If we want to count the number of unique departments, we can combine the COUNT function with DISTINCT:

SELECT COUNT(DISTINCT name) AS unique_departments
FROM Department;

This query will return the number of unique departments in the university.

3.3. Counting Unique Members

Let’s say we want to count how many unique faculty members are currently active (i.e., where the active field is true). Here’s how we can do it:

SELECT COUNT(DISTINCT id) AS active_faculty
FROM Faculty
WHERE active = TRUE;

This query will count the distinct faculty members who are currently active, identified by their unique id.

3.4. Counting Unique GPAs

If we want to find out how many students have unique GPAs, we can apply COUNT(DISTINCT) to the GPA column in the Student table:

SELECT COUNT(DISTINCT gpa) AS unique_gpas
FROM Student;

This query will count the number of distinct GPAs across all students in the university.

3.5. Counting Unique Courses

To count the number of unique courses offered by a specific department, say, the department with id = 1, we can use the following query:

SELECT COUNT(DISTINCT name) AS unique_courses
FROM Course
WHERE department_id = 1;

This query will return the number of unique courses offered by the department with id = 1.

4. Adding Conditions with WHERE Clause

The WHERE clause in SQL allows the filtering of records based on specific conditions. When counting distinct values, adding conditions can help us focus on subsets of the data that meet particular criteria. We can use the WHERE clause with COUNT(DISTINCT) to count unique values while applying various filters.

4.1. Counting Active Programs

This query returns the number of unique programs that are active as of the current date:

SELECT COUNT(DISTINCT name) AS active_programs
FROM Program
WHERE CURDATE() BETWEEN start_date AND end_date;

4.2. Counting Students Who Enrolled After a Certain Date

To find out how many unique students enrolled after a specific date, we can use the WHERE clause to filter the enrollment_date in the Student table:

SELECT COUNT(DISTINCT id) AS recent_students
FROM Student
WHERE enrollment_date > '2020-01-01';

4.3. Counting Faculty Active in a Specific Timeframe

If we want to know how many unique faculty members were active during a certain period (e.g., between 2020 and 2022), we can filter based on their start_date and end_date:

SELECT COUNT(DISTINCT id) AS active_faculty
FROM Faculty
WHERE start_date <= '2022-12-31' AND (end_date IS NULL OR end_date >= '2020-01-01');

5. Counting Distinct Values with Multiple Conditions

When working with complex datasets, we often need to count distinct values based on more than one condition. SQL allows us to add multiple filters using AND and OR operators within the WHERE clause to count distinct values based on various criteria. This is particularly useful when narrowing down our query results across several fields.

SELECT COUNT(DISTINCT id) AS recent_active_faculty
FROM Faculty
WHERE active = TRUE
  AND start_date > '2021-01-01';

6. Counting Distinct Values Across Multiple Columns

In some scenarios, we may need to count distinct values based on more complex conditions that can’t be easily handled with just the WHERE clause. SQL’s CASE statement allows us to apply conditional logic within our queries, enabling us to create more dynamic counts based on specific criteria.

Let’s say we want to count how many distinct courses are active and how many are inactive. We can use CASE to handle this logic:

SELECT 
    COUNT(DISTINCT CASE WHEN is_active = 'TRUE' THEN id END) AS active_courses,
    COUNT(DISTINCT CASE WHEN is_active = 'FALSE' THEN id END) AS inactive_courses
FROM Course;

7. GROUP BY vs. COUNT DISTINCTs

The GROUP BY clause in SQL allows us to group our data by one or more columns and apply aggregate functions like COUNT(DISTINCT) to each group. This is particularly useful when we want to count distinct values within specific categories or groups, such as counting unique students by department or courses by faculty.

If we want to know how many unique students are enrolled in each department, we can use GROUP BY to group students by their department and count distinct student_id values in each group:

SELECT d.name AS department_name, COUNT(DISTINCT s.id) AS student_count
FROM Student s
JOIN Registration r ON s.id = r.student_id
JOIN Course c ON r.course_id = c.id
JOIN Department d ON c.department_id = d.id
GROUP BY d.name;

8. Conclusion

In this article, we explored several techniques for counting distinct values with conditions in SQL, focusing on a university database as our example. From COUNT(DISTINCT) basics to more advanced techniques using WHERE, CASE, and GROUP BY, we can see how SQL offers powerful tools for analyzing datasets and filtering records based on complex criteria.