sql - How to count Distinct value and date less than in the same line

Question

I need to know how many titles and subtitles I have today and how many are over 60 days old. I need help calculating the titles that over 60 days old. Thought of using something like this: count(distinct [Tracking ID1] case when [Date] < DATEADD(DAY, -60, '1/5/2021') but that did work.

I can't use a where statement because it would affect [Titles] and [Subtitles] columns.

select 
    [Line of Business],
    count([Tracking ID]) as [Total # Subtitles],
    count(distinct [Tracking ID1]) as [Total # Titles], 
    count(case when [Date]   < DATEADD(DAY, -60, '1/5/2021') then 1 else 0 end) as [# Subtitles No Action Yet over 60 days], 
    count(distinct [Tracking ID1] case when [Date]   < DATEADD(DAY, -60, '1/5/2021') as [# Titles No Action Yet over 60 days]
    
from table
where 
[Status] like '%active%'
group by [Line of Business]
order by [Line of Business]

question from:https://stackoverflow.com/questions/65602573/how-to-count-distinct-value-and-date-less-than-in-the-same-line

Answer 1

use count instead of sum :

select 
    [Line of Business],
    count([Tracking ID]) as [Total # Subtitles],
    count(distinct [Tracking ID1]) as [Total # Titles], 
    COUNT(case when [Date] < DATEADD(DAY, -60, '1/5/2021') then 1 else NULL end) as [# Subtitles No Action Yet over 60 days], 
    COUNT(distinct CASE when [Date] < DATEADD(DAY, -60, '1/5/2021') THEN [Tracking ID1] ELSE NULL END) AS [# Titles No Action Yet over 60 days]    
from table
where 
[Status] like '%active%'
group by [Line of Business]
order by [Line of Business]