Case When Distinct value then sum another value?

Question

Piggy backing off another question I had yesterday.

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)

Or maybe my entire approach is wrong...idk

Solution

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)

Which you can see in action in this minimal sqlfiddle example

Comments

[donviti]

and there you go, that's all I needed to do. Thanks a ton @dan and everyone else