SQL Server query - Selecting COUNT(*) with DISTINCT
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 [email protected]_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.
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 [email protected]_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.
Read more... Read less...
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
dt is a derived table.
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)