How can I modify this query to correctly apply SQL COUNT DISTINCT to count only unique program names?

General Discussions
,
1

How can I use SQL COUNT DISTINCT to count unique program names by type and push number?

In SQL Server 2005, I have a table cm_production that stores code deployments with columns like ticket_number, program_type, program_name, and push_number.

Goal:

I need to count all distinct program_name values, grouped by program_type and push_number.

What I have so far:

DECLARE @push_number INT;
SET @push_number = [HERE_ADD_NUMBER];

SELECT DISTINCT COUNT(*) AS Count, program_type AS [Type] 
FROM cm_production 
WHERE push_number = @push_number 
GROUP BY program_type;

However, this counts all program names, not just the distinct ones. How can I modify this query to correctly apply SQL COUNT DISTINCT to count only unique program names?

2

You can use COUNT(DISTINCT column):

DECLARE @push_number INT;
SET @push_number = [HERE_ADD_NUMBER];

SELECT program_type AS [Type], COUNT(DISTINCT program_name) AS UniqueProgramCount
FROM cm_production 
WHERE push_number = @push_number 
GROUP BY program_type;

:point_right: This correctly applies COUNT DISTINCT to count unique program_name values per program_type.

3

You can use a derived table:

DECLARE @push_number INT;
SET @push_number = [HERE_ADD_NUMBER];

SELECT program_type, COUNT(*) AS UniqueProgramCount
FROM (
    SELECT DISTINCT program_name, program_type, push_number 
    FROM cm_production 
    WHERE push_number = @push_number
) AS distinct_programs
GROUP BY program_type;

:point_right: This first filters distinct program_name values and then counts them per program_type.

4

You can also use GROUP BY twice.

DECLARE @push_number INT;
SET @push_number = [HERE_ADD_NUMBER];

WITH DistinctPrograms AS (
    SELECT program_type, program_name
    FROM cm_production 
    WHERE push_number = @push_number
    GROUP BY program_type, program_name
)
SELECT program_type, COUNT(*) AS UniqueProgramCount
FROM DistinctPrograms
GROUP BY program_type;

:point_right: This uses a CTE to pre-group distinct program names, then counts them.