SQL Server query - Selecting COUNT(*) with DISTINCT

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.

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

You have to create a derived table for the distinct columns and then query the count from that table:

SELECT COUNT(*) 
FROM (SELECT DISTINCT column1,column2
      FROM  tablename  
      WHERE condition ) as dt

Here dt is a derived table.