mysql - Want to Calculate Hightest Pay Value For a table? -

i want list of employees have worked on activity has highest total pay value.

don't use code such …where actid = 151…ect

• note: total pay worked activity sum of (total hours worked * matching hourly rate) (e.g. total pay activity 151 10.5 hrs @ $50.75 + 11.5 hrs @ $25 + 3hrs @ $33,)

you must use subquery in solution.

actid   hrsworked   hourlyrate  total pay 163     10             45.5     455 163     8              45.5     364 163     6              45.5     273 151     5              50.75    253.75 151     5.5            50.75    279.125 155     10             30       300 155     10             30       300 165     20             25       500 155     10             30       300 155     8              27       216 151     11.5           25       287.5 151     1              33       33 151     1              33       33 151     1              33       33 

you time , effort appreciated. !!

without knowledge of schema, can provide possible sketch (you'll have compute total pay , provide necessary joins , predicates):

select distinct(employee id) -- reconfigure if more employee id <table(s)>  [where...] { | , } total pay = (select max(total pay)                               <table(s)>                              [where...]); 

i used distinct because it's possible have more 1 activity same max value , overlapping employees. if you're including actid in output, won't need distinct because same employee shouldn't on project twice (unless tracked roles on project in case single employee might have multiple roles - depends on data set).