select count(distinct clientid) as Reassessmentcount
 from StateData
where grant='1234' and interview in ('3month','6month','12month','Discharge')
group by Grant,clientid

Reassessmentcount is 8 in the first sql statment

Select a.grant,sum(a.Baselinecount) as Bascount,sum(a.Reassessmentcount) as reassessmentcount from (
select Grant,
sum(case when interview='base' then 1 else 0 end) as Baselinecount,
sum(Case when interview in('3month','6month','12month','Discharge') then 1
else 0 end) as Reassessmentcount
from StateData
where  grant='1234'
group by Grant,clientid

) A
Group by a.grantno

Reassessmentcount is 12 in the second sql statment

Correct value is 8, can someone please help me how to correct the second sql statement to show correct value.

Thanks.

Probably something to do with the first being a count distinct and the second being a sum

I did that and same results. Showing 12 .

In the first case how did you get 8 if you GROUP BY ClientId? It makes no sense.

Can you post some data and the exact queries? In your first case you don't need to use GROUP BY at all as you're using particular Grant already. For the second query use the same as you were using in the first, e.g.

select Grant,
count(distinct(case when interview='base' then ClientId end) as Baselinecount,
Count(distinct (Case when interview in('3month','6month','12month','Discharge') then ClientId
 end) as ReassessmentCount 
from StateData
where  grant='1234'
GROUP BY Grant

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

My TechNet articles

• Edited by Naomi NModerator 10 hours 6 minutes ago
• Marked as answer by NaveenCR 5 hours 9 minutes ago

I did that and same results. Showing 12 .

What does that mean exactly?  I agree with RyanAB.  Your first query is a distinct.  The second query is a SUM.  In theory, the result of the second query can be the same or higher than the result of the first query.

If you perform the query below, what do you get?

select count(clientid) as Reassessmentcount
from StateData
where grant='1234' and interview in ('3month','6month','12month','Discharge')

You need to provide us with DDL and example data... until you do that everything will be guesses.

Thank you so much.