SQL Server 2017 (Linux)

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table carhealthreport
(
  car_id nvarchar(25)
  ,part_name nvarchar(25)
  ,diagnosis nvarchar(25)

)
insert into carhealthreport values ('car_1',  'windscreen' ,'good') 
                                    ,('car_1',  'brakes'     ,'good')
                                    ,('car_1',  'tyres'      ,'good')
                                    ,('car_1',  'wipers'     ,'good')
                                    ,('car_2',  'windscreen' ,'good')
                                    ,('car_2',  'brakes'     ,'good')
                                    ,('car_2',  'tyres'      ,'threadwornout')
                                    ,('car_2',  'wipers'     ,'replacewiper')
                                    ,('car_3',  'windscreen' ,'good')
                                    ,('car_3',  'brakes'     ,NULL)
                                    ,('car_3',  'tyres'      ,NULL)
                                    ,('car_3',  'wipers'     ,NULL)
                                    ,('car_4',  'windscreen' ,NULL)
                                    ,('car_4',  'brakes'     ,NULL)
                                    ,('car_4',  'tyres'      ,NULL)
                                    ,('car_4',  'wipers'     ,NULL)
                                    ,('car_5',  'windscreen','chipped')
                                    ,('car_5',  'brakes'    ,NULL)
                                    ,('car_5',  'tyres'     ,NULL)
                                    ,('car_5',  'wipers'    ,NULL)
20 rows affected

select car_id,
	case when Sum(case when diagnosis='good' then 1 end) =4 then 'good' 
	else
		case when Sum(case when IsNull(diagnosis,'good')='good' then 1 end) =4 then 'unknown'
		else 'needs_work'
		end
	end Outcome
from carhealthreport
group by car_id
order by car_id


car_id
Outcome


car_1
good

car_2
needs_work

car_3
unknown

car_4
unknown

car_5
needs_work


Warning: Null value is eliminated by an aggregate or other SET operation.
car_id	Outcome
car_1	good
car_2	needs_work
car_3	unknown
car_4	unknown
car_5	needs_work