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

Time:09-29

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)

CodePudding user response:

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(*).

  • Related