SQL Server query - Selecting COUNT(*) with DISTINCT

Question

In SQL Server 2005 I have a table cm_production that lists all the code that's been put into production. The table has a ticket_number, program_type, and program_name and push_number along with some other columns.

GOAL: Count all the DISTINCT program names by program type and push number

What I have so far is:

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

This gets me partway there, but it's counting all the program names, not the distinct ones (which I don't expect it to do in that query). I guess I just can't wrap my head around how to tell it to count only the distinct program names without selecting them. Or something.

Answer 1

You can count all the distinct program names by using push number and program type.

SELECT COUNT(DISTINCT Program_Name) AS Count,

Program_Type AS [Type] 

FROM CM_Production 

WHERE Push_Number=@Push_Number 

GROUP BY Program_Type

• DISTINCT COUNT(*) : It will return a row for each unique count. But, what you want is COUNT(DISTINCT <expression>):This evaluates the expression for each row in a group and returns the number of unique and non-null values.