SQL Server查询 - 选择COUNT（*）与DISTINCT(SQL Server quer

在SQL Server 2005中我有一个表cm_production列出所有的被投入生产的代码。 该表具有TICKET_NUMBER，program_type和程序名和push_number与其他一些列一起。

目标：计算所有按照节目类型和推数DISTINCT程序名

我至今是：

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

这让我中途有，但它的计算所有的程序名称，却没有明显的人（我不希望它在该查询做）。 我想我只是不能换我围​​绕着如何告诉它头上没有选择他们只计算了不同的程序名。 或者其他的东西。

Answer 1:

计算所有按照节目类型和推数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(*)将返回各自独特的计数的行。 你想要的是COUNT(DISTINCT <expression>)一组计算表达式的每一行，并返回唯一的，非空值的数量。

Answer 2:

我需要得到每个不同的值的出现次数。 列载区域资料。 简单的SQL查询我结束了同是：

SELECT Region, count(*)
FROM item
WHERE Region is not null
GROUP BY Region

这将使我的列表一样，说：

Region, count
Denmark, 4
Sweden, 1
USA, 10

Answer 3:

你必须创建一个临时表的不同列，然后从该表中查询计数

SELECT COUNT(*) 
FROM (SELECT DISTINCT column1,column2
      FROM  tablename  
      WHERE condition ) as dt

这里DT是一个临时表

Answer 4:

SELECT COUNT(DISTINCT program_name) AS Count, program_type AS [Type] 
FROM cm_production 
WHERE push_number=@push_number 
GROUP BY program_type

Answer 5:

试试这个：

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

Answer 6:

这是你想要得到它存储在最后地址字段的邮递区号的计数一个很好的例子

SELECT DISTINCT
    RIGHT (address, 6),
    count(*) AS count
FROM
    datafile
WHERE
    address IS NOT NULL
GROUP BY
    RIGHT (address, 6)

Answer 7:

select  count (distinct NumTar),'PROPIAS'
from ATM_TRANe with (nolock)
where Fecha>='2014-01-01'
  AND Fecha<='2015-05-31'and NetDestino=0
  and SystemCodResp=0
group by NetDestino 
union 
select sum (contar),'FORANEAS'
from  
(
  select  count(distinct NumTar) as contar
  from ATM_TRANe with (nolock)
  where Fecha>='2014-01-01'
    AND Fecha<='2014-01-31'
    and NetDestino!=0
    and SystemCodResp=0
  group by NetDestino
)dt

文章来源: SQL Server query - Selecting COUNT(*) with DISTINCT