Case When Distinct 值然后求和另一个值?

2021-09-10 00:00:00 sql tsql sql-server-2008 sql-server

小猪退缩了我昨天提出的另一个问题.

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 示例

相关文章