>>>>>This query is returning one record where either Master_IND = 'Y' or we don't have Master_ID = 'Y' so we grab any other record. It also gives us the count of records per Master_Code satisfying these requirements.
>>>>
>>>>Are you saying it works if you make a complex explanation to a complex SQL:) I mean how would you get a result using that query other than 1 for the reccount. You could instead simply say then:
>>>>
>>>>
select MAX(Master_Ind) as master_ind, Master_Code, 1 as reccount ;
>>>>from myTable ;
>>>>group by Master_Code
Cetin
>>>
>>>No, I don't think it will return only 1 record as a count. If the original table has multiple Y records per Master_Code it is supposed to return the number of Y records and if it didn't have Y records, IMHO, it supposed to return number of N records.
>>>
>>>I'm guessing, now I have to setup a quick test case - I'll do in SQL Server.
>>
>>I thought it was obvious that it would return 1 w/o testing but yes test first.
>>Cetin
>
>It returns number of N records in case we don't have Y record as I suspected:
>
>
>declare @C table (name varchar(100), master_code int, master_ind Char(1))
>insert into @C values ('ABC Company', 001, 'Y'),('ABC Comp', 001, 'N'),
>('ABC Cmpny', 001, 'N'),('DEF Comp', 002, 'N'),('DEF Cmpny', 002, 'N'), ('DEF Company', 002, 'Y'),
>('3 cmo',3,'N'), ('3 cmp',3,'N')
>
>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