mysql - Select values compared with a calculated output (an average of another table) -
hopefully can explain properly, output shows ids , average bunch of results each id on table. grabs average of these results should be, , plate number..
now, is, set threshold.. show me ids of average less 50% of should be....
this code
select k.ncr_identifier, avg(j.total_mass) masstotalavg, l.total_combination_mass , k.plate_number iapm_mass_data j inner join iapm_data_link k on j.record_id = k.mass_record_id inner join iacd_ncr_sdtcm_rec l on k.ncr_record_id = l.ncr_id k.ivu_date >= date '2015-03-01' , k.ivu_date <= date '2015-03-31' , l.vehicle_category_code = '12' group k.ncr_identifier
i tried chuck in
and l.total_combination_mass < 0.5(avg(j.total_mass))
but didnt seem it, ideas?
for condition, need use having keyword. please try this:
select k.ncr_identifier, avg(j.total_mass) masstotalavg, l.total_combination_mass , k.plate_number iapm_mass_data j inner join iapm_data_link k on j.record_id = k.mass_record_id inner join iacd_ncr_sdtcm_rec l on k.ncr_record_id = l.ncr_id k.ivu_date >= date '2015-03-01' , k.ivu_date <= date '2015-03-31' , l.vehicle_category_code = '12' group k.ncr_identifier, l.total_combination_mass , k.plate_number having l.total_combination_mass < 0.5(avg(j.total_mass))
Comments
Post a Comment