问题描述
小猪退缩了我昨天提出的另一个问题.
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 值然后求和另一个值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！