Select master_code, Count(*) As 'count' ; >> FROM myTable ; >> GROUP By master_code ; >> INTO Cursor crsCounts >> >>Select t1.*, t2.Count ; >> From myTable t1 ; >> INNER Join crsCounts t2 ; >> ON t1.master_code == t2.master_code ; >> WHERE t1.master_ind = 'Y' Or t2.Count = 1 >>>>PS: Having multiple SQL is both more manageable/readadble plus it is fast. Don't seek single SQL versions IMHO.
select max(Master_ind) as Master_Ind, Master_Code, count(*) as RecCount from myTable T1 where Master_Ind = 'Y' or not exists(select 1 from myTable T where T.Master_Code = T1.Master_Code and Master_Ind = 'Y') group by Master_Code