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

General Discussions
,
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.