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

I am trying to calculate the titles & subtitles that over 60 days old. I tried this query but it didnt work.

count(distinct [Tracking ID1] case when [Date] < DATEADD(DAY, -60, '1/5/2021')

enter image description here

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]
sqlsql-server


Solution 1:[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]

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1

Related Questions