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_typeDISTINCT 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 itemWHERE Region is not nullGROUP BY RegionWhich would give me a list like, say:
Region, countDenmark, 4Sweden, 1USA, 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 dtHere dt is a derived table.