IF Else within a Case statement

In my query below i have the results ,The thing to observe in the result set it for the name "Acevedo" , "Abeyta" its not doing a group by and populating the results in the following column.Rather its addind a new row and adding it as 1 in the next row.
I have to populate the counts in one row for common names.Shall i use a if condition within a case block.If yes how?any other work arounds would be appriciated.
Please help
Thanks

select
isnull(replace(Ltrim(Rtrim(P.Lastname)),',',''),'') Lastname
, case ProductID
WHEN 22 then count(S.Product)
Else 0
END AS Builders
, case ProductID
WHEN 23 then count(S.Product)
Else 0
END AS Associates
, case ProductID
WHEN 24 then count(S.Product)
Else 0
END AS Affiliates
FROM vwpersons p with (nolock)
join vwSubscriptions S with (nolock)
on S.RecipientID = P.ID
where P.Lastname in (select Ltrim(Rtrim(H.name)) from externaldata.dbo.Hispanicnames H)
group by P.Lastname, S.ProductID
having count(P.LastName)>=1
order by 1

Result set :

Firstname Builders AssociatesAffiliates

Abarca010
Abascal200
Abelar100
Abeyta100
Abeyta010
Abreu100
Abreu010
Acevedo100
Acevedo050

You should find a response to much the same question here on this board not more than a few days ago. It had the exact same table and column names.

Wink wink, nudge nudge.

Hi Adriaan
I have worked on the query and have exact results as desired except for this one.
would appreciate if you can take a look.No matter the table is the same but the issue is different.
thanks

Okay, fair enough.

You don't seem to understand the concept of GROUP BY terribly well, though. Each distinct combination of values on the columns in the GROUP BY clause will get its own row in the resultset.

Yeh I agree with you but in my scenario is there a alternate approach to get the count of the common name in one row.

Not sure that this is really a new question ...

Did you test the exact queries that we proposed in the previous thread? If so, please copy them here and explain what was wrong with the results that they gave you.

I have simplified the queries which i was given by you and twan to the above one to get the same results but my issue here is with the case statement .I am almost there .Please take a look at the case block which i need to modify to get counts under diff category for the same name like group by name.Take a look at the resultset for a clear picture.
thanks

Why did you simplify the queries? What was wrong with the results from the complex queries?

Hi this is the one i tried and it worked.<br /><br />select<br />isnull(replace(Ltrim(Rtrim(P.Lastname)),',',''),'' ) Lastname,<br />SUM(case ProductID WHEN 22 then 1 Else 0 END) AS Builders,<br />SUM(case ProductID WHEN 23 then 1 Else 0 END) AS Associates,<br />SUM(case ProductID WHEN 24 then 1 Else 0 END) AS Affiliates<br />FROM vwpersons p with (nolock)<br />join vwSubscriptions S with (nolock)<br />on S.RecipientID = P.ID<br />where P.Lastname in (select Ltrim(Rtrim(H.name)) from externaldata.dbo.Hispanicnames H)<br />andS.ProductID in (22,23,24)<br />group by P.Lastname<br />having count(P.LastName)&gt;=1<br />order by 1<br /><br />[^][^][^][<img src='/community/emoticons/emotion-1.gif' alt='' />]