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, 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

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

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(*) will return a row for each unique count. What you want is COUNT(DISTINCT <expression>): evaluates expression for each row in a group and returns the number of unique, non-null values.

Answer 2

I needed to get the number of occurrences of each distinct value. The column contained Region info. The simple SQL query I ended up with was:

SELECT Region, count(*) FROM item WHERE Region is not null GROUP BY Region 

Which would give me a list like, say:

Region, count Denmark, 4 Sweden, 1 USA, 10