count用法 - sql sum




SQL Server查詢-使用DISTINCT選擇COUNT(*) (5)

在SQL Server 2005中,我有一個表cm_production,列出了所有已投入生產的代碼。 該表有一個ticket_number,program_type,和program_name和push_number以及其他一些列。

目標:按程序類型和推送號碼統計所有的DISTINCT程序名稱

我到目前為止是:

SELECT DISTINCT COUNT(*) AS Count, program_type AS [Type] 
FROM cm_production 
WHERE [email protected]_number 
GROUP BY program_type

這使我在那裡,但它計數所有的程序名稱,而不是獨特的(我不希望它在該查詢中)。 我想我無法圍繞如何讓它只計算不同的程序名而不選擇它們。 或者其他的東西。


按程序類型和推送號碼計算所有DISTINCT程序名稱

SELECT COUNT(DISTINCT program_name) AS Count,
  program_type AS [Type] 
FROM cm_production 
WHERE [email protected]_number 
GROUP BY program_type

DISTINCT COUNT(*)將為每個唯一計數返回一行。 你想要的是COUNT(DISTINCT 表達式 :計算組中每一行的表達式並返回唯一的非空值的數量。


嘗試這個:

SELECT
    COUNT(program_name) AS [Count],program_type AS [Type]
    FROM (SELECT DISTINCT program_name,program_type
              FROM cm_production 
              WHERE [email protected]_number
         ) dt
    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

這是一個很好的例子,您想要獲取存儲在最後一個地址字段中的Pincode的數量

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

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




tsql