sql - How to get summary values from ranked rows? -

given data, how can write sql return 1 row each department showing summaries , counts ranked rows?

with data_row  ( select '123' emp_id, 'aaa' dept_id, 'm' emp_gender, 'a' seqno, 400 score dual union   select '123' emp_id, 'aaa' dept_id, 'm' emp_gender,'b' seqno, 500 score dual union   select '123' emp_id, 'aaa' dept_id, 'm' emp_gender,'c' seqno, 300 score dual union   select '345' emp_id, 'aaa' dept_id, 'f' emp_gender,'a' seqno, 600 score dual union   select '345' emp_id, 'aaa' dept_id, 'f' emp_gender,'b' seqno, 700 score dual union   select '222' emp_id, 'bbb' dept_id, 'f' emp_gender,'a' seqno, 200 score dual union   select '222' emp_id, 'bbb' dept_id, 'f' emp_gender,'b' seqno, 800 score dual   ) select  dept_id,         sum(score) on ( partition dept_id) rnk_1_sum,         count(*)   on (partition dept_id) dept_count,         count(*)   on (partition emp_gender) male_count,         count(*)   on (partition emp_gender) female_count    (  select  emp_id,                     dept_id,                     seqno,                     score,                         emp_gender,   rank() on (partition emp_id                      order seqno) rnk data_row       )                 rnk=1 -- group dept_id  -- including line yields  ora-00979: not group expression 

desired results

dept_id  rnk_1_sum dept_count male_count female_count ------- ---------- ---------- ---------- ------------ aaa           1000          2          1            1 /* 123.score  + 345.score = 400 + 600 */ bbb            200          1          0            1 /* 222.score = 200 */ 

just use aggregation:

with data_row  (   select '123' emp_id, 'aaa' dept_id, 'm' emp_gender, 'a' seqno, 400 score dual union   select '123' emp_id, 'aaa' dept_id, 'm' emp_gender,'b' seqno, 500 score dual union   select '123' emp_id, 'aaa' dept_id, 'm' emp_gender,'c' seqno, 300 score dual union   select '345' emp_id, 'aaa' dept_id, 'f' emp_gender,'a' seqno, 600 score dual union   select '345' emp_id, 'aaa' dept_id, 'f' emp_gender,'b' seqno, 700 score dual union   select '222' emp_id, 'bbb' dept_id, 'f' emp_gender,'a' seqno, 200 score dual union   select '222' emp_id, 'bbb' dept_id, 'f' emp_gender,'b' seqno, 800 score dual   ) select  dept_id,         sum(score) rnk_1_sum,         count(*)  dept_count,         sum(case when emp_gender = 'm' 1 else 0 end) male_count,         sum(case when emp_gender = 'f' 1 else 0 end) female_count data_row       group dept_id; 

here sql fiddle.

edit:

is want?

with data_row  (   select '123' emp_id, 'aaa' dept_id, 'm' emp_gender, 'a' seqno, 400 score dual union   select '123' emp_id, 'aaa' dept_id, 'm' emp_gender,'b' seqno, 500 score dual union   select '123' emp_id, 'aaa' dept_id, 'm' emp_gender,'c' seqno, 300 score dual union   select '345' emp_id, 'aaa' dept_id, 'f' emp_gender,'a' seqno, 600 score dual union   select '345' emp_id, 'aaa' dept_id, 'f' emp_gender,'b' seqno, 700 score dual union   select '222' emp_id, 'bbb' dept_id, 'f' emp_gender,'a' seqno, 200 score dual union   select '222' emp_id, 'bbb' dept_id, 'f' emp_gender,'b' seqno, 800 score dual   ) select  dept_id,         sum(score) rnk_1_sum,         count(*)  dept_count,         sum(case when emp_gender = 'm' 1 else 0 end) male_count,         sum(case when emp_gender = 'f' 1 else 0 end) female_count (select e.*, row_number() on (partition emp_id order seqno) seqnum         data_row          ) d seqnum = 1 group dept_id;