Please help me here with below Query
PlanCount = COUNT(Model_no),
PlanName = Case COUNT(Model_no) When 1 then a.Model_no when 0 then NULL else 'Multi Model' end
FROM dbo.cpDetails D LEFT JOIN TBACTDetails A ON D.AcctNo = A.Social
WHERE model_NAME IS NULL
GROUP BY AcctNo --, Case when COUNT(Model_no)= 1 then a.Model_no else 'Multi Model' end
ORDER BY 2 DESC,1
i got below error
---Column 'TBACTDetails.Model_no' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
then i placed max(a.model) it is worked, i do get results but the problem
First Question is is is it correct way of doing or any otherway without use Max() function ?
Second Question is along with abouve results, if COUNT(model_no) > 1 then (i.e., this model has more than one model name values in model name columns) how can i show both values in one column (seperated by ; )
in the results i would like to get as
100,2,'Multi Model','Zirrard; ModaChed'
Please assist me how to get this new column ('Zirrard; ModaChed') where count(*) > 1
Thanks in advance