代码之家 › 专栏 › 技术社区 › somacore

SQL Server查询-使用distinct选择count(*)

tsql sql-server-2005 sql-server sql

347

somacore · 技术社区 · 16 年前

在SQLServer2005中,我有一个表cm_production,它列出了所有已投入生产的代码。该表有票据编号、程序类型、程序名称和推送编号以及其他一些列。

目标:按程序类型和推送数统计所有不同的程序名

到目前为止,我拥有的是:

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

这让我有了一段路,但它计算的是所有的程序名,而不是不同的程序名(我不希望在那个查询中这样做)。我想我就是不能在不选择程序名的情况下告诉它只计算不同的程序名。或者什么的。

7 回复 | 直到 6 年前

604

Liam Joshua 6 年前

按程序类型和推送数统计所有不同的程序名

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>) :为组中的每一行计算表达式,并返回唯一的非空值的数目。

Netsi1964 8 年前

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

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

这会给我一个列表,比如:

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

Farvardin kiran 10 年前

您必须为不同的列创建一个临时表,然后从该表查询计数。

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

这是温度表

van 16 年前

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

KM. 16 年前

试试这个:

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

-1

Faisal 8 年前

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

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

-5

Taryn Frank Pearson 10 年前

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