By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TABLE dept_vw (id, dept, name, from_Date, to_Date, mi, avg, dept_id) AS
SELECT 12, 'CSE', 'BRADLEY', DATE '2015-04-01', DATE '2015-06-25', 'CH', 3.69, 70 FROM DUAL UNION ALL
SELECT 22, 'CSE', 'ASHLEY', DATE '2015-02-01', DATE '2015-06-15', 'EN', NULL, 70 FROM DUAL UNION ALL
SELECT 30, 'CSE', 'GARCES', DATE '2014-04-01', DATE '2015-02-16', 'TR', 3.77, 70 FROM DUAL UNION ALL
SELECT 25, 'CSE', 'LYNCH', DATE '2014-10-04', DATE '2015-02-15', 'CH', 3.08, 70 FROM DUAL UNION ALL
SELECT 35, 'CSE', 'STRONG', DATE '2014-11-27', DATE '2015-02-15', 'CH', 4.08, 70 FROM DUAL UNION ALL
SELECT 37, 'CSE', 'KEFFLER', DATE '2015-01-01', DATE '2015-01-02', 'CH', NULL, 70 FROM DUAL UNION ALL
SELECT 34, 'CSE', 'STRONG', DATE '2014-04-01', DATE '2014-10-26', 'TD', 4, 70 FROM DUAL UNION ALL
SELECT 24, 'CSE', 'LYNCH', DATE '2014-04-01', DATE '2014-08-23', 'TD', 3, 70 FROM DUAL UNION ALL
SELECT 33, 'CSE', 'STRONG', DATE '2014-02-11', DATE '2014-03-31', 'AN', NULL, 70 FROM DUAL UNION ALL
SELECT 29, 'CSE', 'GARCES', DATE '2013-12-11', DATE '2014-03-31', 'AN', 3.54, 70 FROM DUAL UNION ALL
SELECT 23, 'CSE', 'LYNCH', DATE '2014-02-08', DATE '2014-03-31', 'AN', NULL, 70 FROM DUAL UNION ALL
SELECT 21, 'CSE', 'SHI', DATE '2013-04-01', DATE '2014-01-13', 'CH', 3.69, 70 FROM DUAL UNION ALL
SELECT 28, 'CSE', 'DENNEY', DATE '2013-09-01', DATE '2014-01-13', 'CH', 3.92, 70 FROM DUAL UNION ALL
SELECT 18, 'CSE', 'SAVOY', DATE '2013-04-01', DATE '2013-11-14', 'EN', NULL, 70 FROM DUAL UNION ALL
SELECT 27, 'CSE', 'WHITE', DATE '2013-04-01', DATE '2013-06-28', 'GC', 2, 70 FROM DUAL UNION ALL
SELECT 26, 'CSE', 'WHITE', DATE '2013-01-01', DATE '2013-03-31', 'AN', 2.77, 70 FROM DUAL UNION ALL
SELECT 17, 'CSE', 'SAVOY', DATE '2013-02-01', DATE '2013-03-31', 'AN', 2.54, 70 FROM DUAL UNION ALL
SELECT 20, 'CSE', 'SHI', DATE '2013-02-01', DATE '2013-03-31', 'AN', 3.15, 70 FROM DUAL;
18 rows affected
SELECT id,
MAX(dept) AS dept,
CASE GROUPING_ID(dept_id, id) WHEN 0 THEN MAX(name) END AS name,
CASE GROUPING_ID(dept_id, id) WHEN 0 THEN MAX(from_date) END AS from_date,
CASE GROUPING_ID(dept_id, id) WHEN 0 THEN MAX(to_date) END AS to_date,
CASE GROUPING_ID(dept_id, id) WHEN 0 THEN MAX(mi) END AS mi,
ROUND(AVG(avg), 2) AS avg,
dept_id
FROM dept_vw vw
GROUP BY ROLLUP(dept_id, id)
HAVING GROUPING_ID(dept_id, id) IN (0, 1)
ORDER BY
dept_id,
GROUPING_ID(dept_id, id),
from_date DESC;
| ID | DEPT | NAME | FROM_DATE | TO_DATE | MI | AVG | DEPT_ID |
|---|---|---|---|---|---|---|---|
| 12 | CSE | BRADLEY | 2015-04-01 00:00:00 | 2015-06-25 00:00:00 | CH | 3.69 | 70 |
| 22 | CSE | ASHLEY | 2015-02-01 00:00:00 | 2015-06-15 00:00:00 | EN | null | 70 |
| 37 | CSE | KEFFLER | 2015-01-01 00:00:00 | 2015-01-02 00:00:00 | CH | null | 70 |
| 35 | CSE | STRONG | 2014-11-27 00:00:00 | 2015-02-15 00:00:00 | CH | 4.08 | 70 |
| 25 | CSE | LYNCH | 2014-10-04 00:00:00 | 2015-02-15 00:00:00 | CH | 3.08 | 70 |
| 30 | CSE | GARCES | 2014-04-01 00:00:00 | 2015-02-16 00:00:00 | TR | 3.77 | 70 |
| 34 | CSE | STRONG | 2014-04-01 00:00:00 | 2014-10-26 00:00:00 | TD | 4 | 70 |
| 24 | CSE | LYNCH | 2014-04-01 00:00:00 | 2014-08-23 00:00:00 | TD | 3 | 70 |
| 33 | CSE | STRONG | 2014-02-11 00:00:00 | 2014-03-31 00:00:00 | AN | null | 70 |
| 23 | CSE | LYNCH | 2014-02-08 00:00:00 | 2014-03-31 00:00:00 | AN | null | 70 |
| 29 | CSE | GARCES | 2013-12-11 00:00:00 | 2014-03-31 00:00:00 | AN | 3.54 | 70 |
| 28 | CSE | DENNEY | 2013-09-01 00:00:00 | 2014-01-13 00:00:00 | CH | 3.92 | 70 |
| 21 | CSE | SHI | 2013-04-01 00:00:00 | 2014-01-13 00:00:00 | CH | 3.69 | 70 |
| 18 | CSE | SAVOY | 2013-04-01 00:00:00 | 2013-11-14 00:00:00 | EN | null | 70 |
| 27 | CSE | WHITE | 2013-04-01 00:00:00 | 2013-06-28 00:00:00 | GC | 2 | 70 |
| 17 | CSE | SAVOY | 2013-02-01 00:00:00 | 2013-03-31 00:00:00 | AN | 2.54 | 70 |
| 20 | CSE | SHI | 2013-02-01 00:00:00 | 2013-03-31 00:00:00 | AN | 3.15 | 70 |
| 26 | CSE | WHITE | 2013-01-01 00:00:00 | 2013-03-31 00:00:00 | AN | 2.77 | 70 |
| null | CSE | null | null | null | null | 3.33 | 70 |