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.
6 Answers
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.
SELECT COUNT(DISTINCT program_name) AS Count, program_type AS [Type] FROM cm_production WHERE push_number=@push_number GROUP BY program_type try this:
SELECT COUNT(program_name) AS [Count],program_type AS [Type] FROM (SELECT DISTINCT program_name,program_type FROM cm_production WHERE push_number=@push_number ) dt GROUP BY program_type This is a good example where you want to get count of Pincode which stored in the last of address field
SELECT DISTINCT RIGHT (address, 6), count(*) AS count FROM datafile WHERE address IS NOT NULL GROUP BY RIGHT (address, 6) 
