# Distinct counts or sums

#1

I have the following code:

Select distinct Convert(date,op.Oppo_CreatedDate) as Created, Convert(date,o.Oppo_Opened) as Opened,
Sum(Case When op.Oppo_Stage In ('IQ','HQ','3Q') Then 1 Else 0 End) as [3Hour],
Sum(Case When op.Oppo_Stage In ('SDQ','24Q') Then 1 Else 0 End) as [24Hour]
From OpportunityProgress op Inner Join Opportunity o On o.Oppo_OpportunityId = op.Oppo_OpportunityId
Where o.Oppo_Deleted is Null and o.Oppo_ChannelId=7 and Convert(date,op.Oppo_CreatedDate) > DateAdd(Day, -30, Getdate())
Group By Convert(date,op.Oppo_CreatedDate), Convert(date,o.Oppo_Opened)

The values calculated in [3hour] and [24hour] are not accurate as the join between the tables have more than 1 record. Is it possible to a distinct sum calculation to eliminate the duplication.

#2
``````Select op.Created, Convert(date,o.Oppo_Opened) as Opened,
Max([3Hour]) as [3Hour],
Max([24Hour]) as [24Hour]
From (
Select Oppo_OpportunityId,
Convert(date, Oppo_CreatedDate) as Created,
Sum(Case When Oppo_Stage In ('IQ','HQ','3Q') Then 1 Else 0 End) as [3Hour],
Sum(Case When Oppo_Stage In ('SDQ','24Q') Then 1 Else 0 End) as [24Hour]
From OpportunityProgress
Where op.Oppo_CreatedDate > DateAdd(Day, -30, Convert(date, getdate()))
Group By Oppo_OpportunityId, Convert(date, Oppo_CreatedDate)
) as op
Inner Join Opportunity o On o.Oppo_OpportunityId = op.Oppo_OpportunityId
Where o.Oppo_Deleted is Null and o.Oppo_ChannelId=7``````