>>>>>select MAX(name) as Name, COUNT(*) as CntRecs, MAX(Master_Ind) as Master_Ind, Master_Code from @C T1
>>>>>where master_ind = 'Y' or not exists(select 1 from @C T where T.master_code = T1.master_code and T.master_ind = 'Y')
>>>>>group by master_code
>>>>
>>>>Sorry but running this test I don't see the results as requested. You have changed the query a bit just for the sake of not seeing a constant 1:)
>>>>Cetin
>>>
>>>IMHO, this is the original query I suggested.
>>>
>>>Re: SQL Select with Group by and Order by Thread #1462119 Message #1462330
>>
>>Really? Ok just change the master_code to 4 for the last company (cmp?). I get constant 1s on all of them:
>>
>>Name CntRecs Master_Ind Master_Code
>>ABC Company 1 Y 1
>>DEF Company 1 Y 2
>>3 cmo 1 N 3
>>3 cmp 1 N 4
>>
>>Do you see something different?
>>Cetin
>
>No, I see the same, but what is the question then? We do indeed have just one N record for master_Code 3 or master_Code 4 and that's what my query is returning - no argument here.
>
>In other words, my query will return number of Ys if we have at least one Y and number of other Master_Ind if we have them and don't have 'Y'.
>It would not return the total number of records per Master_Code, if this was your point.

You are playing with words. He had only one Y per master_code and he wanted to have the count of rows per master_code, not a constant 1 per master_code. See his original question where I replied with a 2 part SQL.
Cetin