SQL query to get the aggregate results using row_number over partition by

I have table like the below

car_id	part_name	diagnosis
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

All cars in this table will have the only four part_names as shown in the table (windscreen,brakes,tyres and wipers).Based on the diagnosis field I want to categories the cars.

if a car has all diagnosis as good then outcome is good
if a car has atleast one diagnosis that is not good and not NULL then the outcome is needs_work
if a car has all 4 diagnosis as NULL or (a combination of NULL and good diagnosis) then outcome is unknown

Based on the conditions above the outcome field in the resultset will be as follows

car_1 has 4 good diagnosis so the outcome is good
car_2 has 2 diagnosis that are not good so the outcome is needs_work
car_3 has 1 good diagnosis and 3 NULLS so the outcome is unknown
car_4 has all 4 diagnosis as NULl so the outcome is unknown
car_5 has one diagnosis that is not good so the outcome is needs-work

so the resultset should be like the table below

car_id	outcome
car_1	good
car_2	needs_work
car_3	unknown
car_4	unknown
car_5	needs_work

I tried with ROW_NUMBER() OVER (PARTITION BY c.car_id ORDER BY c.diagnosis DESC) but I am not getting the desired result I want.

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)

Answer

I think you can get your desired results with just an aggregate and conditional case expressions

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

Demo Fiddle

This assumes always 4 rows per car_Id, if this is variable you could use count(*).

Advertisement

Answer