选择带有DISTINCT的COUNT(*)

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

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

到目前为止，我有:

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

这让我在那里的一部分，但它是计数所有的程序名称，而不是不同的(我不希望它在该查询中这样做)。我想我只是不知道如何告诉它只计算不同的程序名称而不选择它们。什么的。

1337010 次浏览
小开

试试这个:

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
小开
最佳答案

按程序类型和推送号计算所有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>):计算组中每一行的表达式，并返回唯一的非空值的数量。

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

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

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

它会给我一个列表，比如:

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

你必须为不同的列创建一个派生表，然后从该表查询计数:

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

这里dt是一个派生表。

小开

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

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

您可以尝试以下查询。

SELECT column1,COUNT(*) AS Count
FROM tablename where createddate >= '2022-07-01'::date group by column1

提交答案