CODEWITHSUNDEEP
sqlcountdistinctcase
I'm Root James
I'm Root James

Reputation: 6525

Using distinct and case for two separate columns

I am counting cases by year using the following code:

COUNT(CASE WHEN Year(FilingDate)=2008 THEN 1 ELSE NULL END) AS '2008'

and I want to only count these cases when another column is distinct. The other column is called 'FilingDate' What I imagine would look something like this:

COUNT(CASE distinct (DocketNumber) WHEN Year(FilingDate)=2008 THEN 1 ELSE NULL END) AS '2008',

The Sample Records:

DocketNumber FilingDate

123 2008

123 2008

123 2008

111 2009

112 2009

I would just like to recieve = 1 Any Ideas?

Thanks

Upvotes: 1

Views: 413

Answers (2)

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

Use option with SUBQUERY and GROUP BY clause

SELECT COUNT(CASE WHEN YearFilingDate = 2008 THEN 1 END) AS '2008'
FROM
 (      
  SELECT Year(FilingDate) AS YearFilingDate, DocketNumber
  FROM dbo.test55
  GROUP BY Year(FilingDate), DocketNumber
  ) x

Demo on SQLFiddle

Upvotes: 1

Tony Shih
Tony Shih

Reputation: 436

SELECT COUNT(CASE WHEN Year(FilingDate)=2008 THEN 1 ELSE NULL END) AS '2008'
GROUP BY DocketNumber

may be.

Upvotes: 0

Related Questions