SQL Server查询-选择计数(*)与不同

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.

在SQL Server 2005中，我有一个表cm_production，它列出了投入生产的所有代码。该表具有ticket_number、program_type、program_name和push_number以及其他一些列。

GOAL: Count all the DISTINCT program names by program type and push number

目的:根据程序类型和推号计算所有不同的程序名

What I have so far is:

到目前为止，我所拥有的是:

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.

这让我得到了partway，但是它在计算所有程序名，而不是不同的程序名(我不希望它在这个查询中执行)。我想我就是想不出怎么告诉它只数不同的程序名而不选择它们。什么的。

7 个解决方案

#1

516

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, nonnull values.

不同的COUNT(*)将返回每个惟一计数的行。您需要的是COUNT(不同的表达式):为组中的每一行计算表达式，并返回唯一的非空值的数量。

#2

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:

我需要得到每个不同值出现的次数。列包含区域信息。我最后得到的简单SQL查询是:

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

#3

You have to create a temp 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 temp table

这里dt是一个临时表

#4

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

#5

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

#6

-1

This is a good example where you want to get count of Pincode which stored in the last of address field

这是一个很好的例子，您希望获得存储在地址字段最后的Pincode的计数

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

#7

-5

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

#1

516