Selecting COUNT(*) with DISTINCT
SqlSql ServerSql Server-2005TsqlSql Problem Overview
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.
Sql Solutions
Solution 1 - Sql
> 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.
Solution 2 - Sql
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
Solution 3 - Sql
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.
Solution 4 - Sql
SELECT COUNT(DISTINCT program_name) AS Count, program_type AS [Type]
FROM cm_production
WHERE push_number=@push_number
GROUP BY program_type
Solution 5 - Sql
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
Solution 6 - Sql
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)