SQL Server 查询 - 使用 DISTINCT 选择 COUNT(*)
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1521605/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL Server query - Selecting COUNT(*) with DISTINCT
提问by somacore
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
目标：按程序类型和推送数量计算所有 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.
这让我走到了一半，但它计算了所有程序名称，而不是不同的名称（我不希望它在该查询中这样做）。我想我只是无法理解如何告诉它只计算不同的程序名称而不选择它们。或者其他的东西。
回答by Remus Rusanu
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. What you want is COUNT(DISTINCT <expression>): evaluates expression for each row in a group and returns the number of unique, non-null values.
DISTINCT COUNT(*)将为每个唯一计数返回一行。您想要的是COUNT(DISTINCT <expression>)：评估组中每一行的表达式并返回唯一的非空值的数量。
回答by Netsi1964
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
回答by venkatesh
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 dtis a derived table.
这dt是一个派生表。
回答by van
SELECT COUNT(DISTINCT program_name) AS Count, program_type AS [Type]
FROM cm_production
WHERE push_number=@push_number
GROUP BY program_type
回答by KM.
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
回答by Uday Phadke
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)
回答by Uday Phadke
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
