SQL Server query - Selecting COUNT with DISTINCT

Question

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 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

User · Accepted Answer

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.

User · Answer

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

User · Answer

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

User · Answer

SELECT COUNT DISTINCT program name  AS Count  program type AS  Type   FROM cm production  WHERE push number  push number  GROUP BY program type

User · Answer

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

User · Answer

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

[sql] SQL Server query - Selecting COUNT(*) with DISTINCT

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005

Examples related to tsql