sql - Oracle group by functions -

i have table following values.

 read_count    users      manager ---------------------------------- 16            ann        jake 12            ann        jake 19            tom        martin 

i trying group values based on manager , take sum of maximum read_count per user.

something

select manager,sum(max(read_count)) table group manager 

(i know group doesn't work. gave here better understanding)!

here 1 approach. use row_number() enumerate rows each user/manager combination in descending order read_count. then, use condition sum 1 value per user:

select manager,        sum(case when seqnum = 1 read_count end) summaxreadcountperuser (select t.*,              row_number() on (partition manager, users order read_count desc                                ) seqnum       table t      ) t group manager; 

you can nested group by statements:

select manager, sum(max_read_count) (select manager, users, max(read_count) max_read_count       table t       group manager, users      ) mu group manager; 

i prefer first method because generalizes more easily, if want sum of 2 highest values.