To count the distinct program names by program type and push number in SQL Server 2005, you can use the COUNT DISTINCT function along with a GROUP BY clause. Here is an example query that should achieve the desired result:

SQL

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

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

In this query, we first declare and set the value of the variable @push_number to the desired push number. We then select the program type, push number, and the count of distinct program names for each combination of program type and push number.

The COUNT(DISTINCT) function is used to count the number of distinct program names for each group. By grouping the results by program type and push number using the GROUP BY clause, we ensure that the count is calculated for each unique combination of program type and push number.

Note that we include both program type and push number in the SELECT statement, as well as the GROUP BY clause. This is necessary to ensure that we get distinct counts for each combination of program type and push number.

This query should provide you with the desired result, showing the count of distinct program names for each program type and push number combination. If you need to filter the results further, you can add additional WHERE clauses or use the HAVING clause to filter the results based on aggregate functions.

It's important to note that using DISTINCT and GROUP BY clauses can impact the performance of your query, especially on large datasets. So, if performance is a concern, you may want to consider indexing or other optimization techniques to improve query speed.

In summary, by using the COUNT DISTINCT function along with the GROUP BY clause, you can count the distinct program names by program type and push number in SQL Server 2005.