問題描述
小豬退縮了我昨天提出的另一個(gè)問題.
Piggy backing off another question I had yesterday.
我想知道如何計(jì)算 amt > 1500 的不同記錄數(shù).根據(jù)我的數(shù)據(jù)連接方式，我可以多次反映相同的 PKey AcctNo，因?yàn)槲业耐暾獠窟B接到另一個(gè)具有多個(gè)事務(wù)記錄的表.
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
這是我當(dāng)前的代碼，可產(chǎn)生所需的結(jié)果.我
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)
也許我的整個(gè)方法都是錯(cuò)誤的...idk
Or maybe my entire approach is wrong...idk
推薦答案
您可以對 CASE 表達(dá)式的輸出使用 COUNT(DISTINCT ).例如，要計(jì)算具有 [amount] [amount] 的不同 行，您可以使用它:AcctNo_Pkey 的數(shù)量.聚合結(jié)果中某處的 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)
您可以在在這個(gè)最小的 sqlfiddle 示例
這篇關(guān)于Case When Distinct 值然后求和另一個(gè)值?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！