問題描述
小豬退縮了我昨天提出的另一個問題.
Piggy backing off another question I had yesterday.
我想知道如何計算 amt > 1500 的不同記錄數.根據我的數據連接方式，我可以多次反映相同的 PKey AcctNo，因為我的完整外部連接到另一個具有多個事務記錄的表.
I was wondering how I would go about counting the distinct number of records that have an amt > 1500. The way my data is joined, I could have the same PKey AcctNo reflected more than one time because my full outer joined to another table that has multiple transactional records.
(Case When AcctNo_PKey = distinct then sum(case when amount > 1500 then 1 else 0 end)
else 0) end as GT1500
這是我當前的代碼，可產生所需的結果.我
this my current code that produces a desired result. I
SELECT sum(case when amount > 1500 then 1 else 0 end) as GT1500
, sum(case when amount < 1500 then 1 else 0 end) as LT1500
, DATEPART(Year, amount.Date) Deposit_Year
, DATEPART(QUARTER, amount.Date) Deposit_Qtr
From account
full outer JOIN amount ON account.AcctNo = amount.AcctNo
group by DATEPART(Year, amount.Date)
, DATEPART(QUARTER, amount.Date)
也許我的整個方法都是錯誤的...idk
Or maybe my entire approach is wrong...idk
推薦答案
您可以對 CASE 表達式的輸出使用 COUNT(DISTINCT ).例如，要計算具有 [amount] [amount] 的不同 行，您可以使用它:AcctNo_Pkey 的數量.聚合結果中某處的 1500
You can use COUNT(DISTINCT ) on the output of a CASE expression. For example, to count the number of distinct AcctNo_Pkeys that have an [amount] < 1500 row somewhere in the aggregated result, you could use this:
COUNT(DISTINCT CASE WHEN [amount] < 1500 THEN AcctNo_PKey END)
您可以在在這個最小的 sqlfiddle 示例
這篇關于Case When Distinct 值然后求和另一個值?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！