sql - SQL Server查询 - 使用DISTINCT选择COUNT（*）(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. (在SQL Server 2005中，我有一个表cm_production，其中列出了所有已投入生产的代码。) The table has a ticket_number, program_type, and program_name and push_number along with some other columns. (该表有ticket_number，program_type，program_name和push_number以及其他一些列。)

GOAL: Count all the DISTINCT program names by program type and push number (目标：按程序类型和推送号计算所有DISTINCT程序名称)

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. (或者其他的东西。)

  ask by somacore translate from so

Answer 1

Count all the DISTINCT program names by program type and push number (按程序类型和推送编号计算所有DISTINCT程序名称)

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. (DISTINCT 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. (你想要的是COUNT(DISTINCT <expression>) ：计算组中每一行的表达式，并返回唯一的非空值的数量。)