SQL: Count Distinct with a Condition Based on a Different Column

In this article, we’ll delve into the world of SQL and explore how to achieve a distinct count based on a condition applied to a different column. We’ll examine the provided Stack Overflow post, understand the challenges, and develop a solution using various approaches.

Introduction

SQL (Structured Query Language) is a standard language for managing relational databases. Its primary function is to manage data stored in databases. SQL queries are used to perform various operations such as creating, modifying, and querying database records.

The question presented in the Stack Overflow post involves counting distinct units associated with each qualification while also accounting for exams that have been passed by candidates. We’ll explore how to achieve this using different approaches and discuss the concepts involved.

Understanding the Challenge

Let’s break down the challenge:

• Distinct Units: We need to count the number of unique units associated with each qualification.
• Passed Exams: However, some exams have been passed multiple times by a candidate. We want to ensure that we only count each exam once, even if it has been passed multiple times.

Approach 1: Using COUNT(DISTINCT) and CASE Statements

The initial answer provided uses the following query:

SELECT candidate, qualification,
       COUNT(DISTINCT units) AS total_units,
       COUNT(DISTINCT CASE WHEN exam_status = 'Passed' THEN exam END) AS passed_exams
FROM example_table
GROUP BY candidate, qualification;

This approach works by:

• Counting the distinct values in the units column for each group.
• Using a CASE statement to count the distinct exams where exam_status is 'Passed'.

However, this approach does not guarantee that we’ll only get unique exams passed, as it counts all passes regardless of the exam being repeated.

Approach 2: Using Window Functions

The next answer provided uses window functions:

SELECT candidate, qualification,
       COUNT(DISTINCT unit) AS total_units,
       SUM(CASE WHEN exam_status = 'Passed' AND seqnum = 1 THEN unit END) AS passed_units,
       COUNT(DISTINCT CASE WHEN exam_status = 'Passed' THEN exam END) AS passed_exams
FROM (
  SELECT et.*,
         ROW_NUMBER() OVER (PARTITION BY candidate, qualification, exam
                            ORDER BY (CASE WHEN exam_status = 'Passed' THEN 1 ELSE 2 END)) AS seqnum
  FROM example_table et
) et
WHERE seqnum = 1
GROUP BY candidate, qualification;

This approach works by:

• Using the ROW_NUMBER() function to assign a unique number (seqnum) to each row within each group.
• Ordering these rows based on whether exam_status is 'Passed' or not. This ensures that exams passed earlier are counted first.
• Summing up the units for only those passes where seqnum = 1, effectively removing duplicates.

Approach 3: Using GROUP BY and Subqueries

Another approach could be to use a subquery:

SELECT candidate, qualification,
       COUNT(DISTINCT unit) AS total_units,
       (SELECT SUM(unit)
        FROM example_table et2
        WHERE et2.candidate = e.candidate AND et2.qualification = e.qualification AND et2.exam_status = 'Passed')
AS passed_units,
       COUNT(DISTINCT CASE WHEN exam_status = 'Passed' THEN exam END) AS passed_exams
FROM (
  SELECT candidate, qualification, unit, exam_status
  FROM example_table
) e
GROUP BY candidate, qualification;

This approach works by:

• Creating a subquery that sums up the units for all passes within each group.
• Using this sum as part of the main query’s passed_units count.

Conclusion

SQL provides various methods to solve complex problems like counting distinct units based on a different condition. By exploring different approaches, we can choose the most suitable solution depending on our specific use case. This article provided an in-depth examination of three SQL solutions and offered insights into how each method works.

Last modified on 2023-09-14