Добавить distinct в направление

SELECT DISTINCT <текст запроса>

Уберёт повторяющиеся по всем полям записи;

Для удаления дубликатов по конкретным атрибутам можете попробовать использовать оконную функцию row_number(), которая нумерует строки с повторяющимися указанными атрибутами.

Пример использования для удаления дубликатов по повторяющимся полям Attr1, Attr2 из таблицы #tmp (ID, Attr1, Attr2):

DELETE #tmp
WHERE ID IN
(
    SELECT A.ID
    from (
        SELECT ID, Attr1, Attr2, 
        ROW_NUMBER() OVER
            (   
                PARTITION BY Attr1, Attr2
                ORDER BY ID
            ) as uniqchk
        FROM #tmp
        ) as A
WHERE A.uniqchk <> 1

)

В данном случае, вместо таблицы #tmp будет ваш запрос в качестве подзапроса. Но тогда надо немного обернуть условие из удаления на фильтрацию:

    SELECT ID, Attr1, Attr2
    from (
        SELECT ID, Attr1, Attr2, 
        ROW_NUMBER() OVER
            (   
                PARTITION BY Attr1, Attr2
                ORDER BY ID
            ) as uniqchk
        FROM #tmp
        ) WHERE uniqchk = 1

Примерно как-то так. Извините, но повозиться с синтаксисом придется вам, либо кому-то поправить мой ответ, потому что я уже давно не практиковался с sql. Я стремлюсь помочь вам подсказав нестандартный подход к логике решения удаления дубликатов.

Если у вас есть дубли то - у вас неправильно записано выражение в Group by. Я не знаю как у вас организована работа, и кто придумал в Group by поставить ф-цию (udp для оракла может быть так можно, но надо дополнительно изучать), но я вам советую её оттуда убрать, или хотя бы если не убрать, то привести в порядок выражение групировки. Иначе вам тут помочь не смогут. Типичный запрос на статистику по полю имеет вид

select group1, count(*) from table1
group by group1

select group1,group2, count(*) from table1
group by group1,group2

select group1, group2, и так далее, count(*) from table1 
group by group1, group2 и так далее` 

если вы приведете выборку к такому виду у вас проблем не будет. group1 и тут и там должно совпадать. Пробую привести вашу выборку к такому виду (я поначалу не знал что у вас оракл, ответ - общий предполагая что среда sql не извесна)

select 
decode(grouping(s.name),1,'все_направления',s.name) as направление, -- тут
count(distinct(s.name)) as cnt, -- это к-во направлений
count(distinct(x.id)) cnt2 -- а может всётаки к-во служащих, а не к-во направлений?
from employees x
left join staff y on x.id_e=y.id_e
left join division z on z.id_d=y.id_d
left join scientific_degree l on x.id_e=l.id_e
left join scientific_direction s on s.id_direction = l.id_direction
group by 
    decode(grouping(s.name),1,'все_направления',s.name) -- и тут должно совпадать    
having count (l.id_direction) > 0 and l.d_start in (select max(sd.d_start)
from scientific_degree sd
where sd.d_start = l.d_start)
order by z.abbreviation,cnt desc;

Данная выборка будет правильна с точки зрения программиста, но вам она вероятнее всего не подойдет.

У меня вопрос, 1)зачем вам к-во подразделений если вы групируете по подразделению. 2)где ваше z в select?

Я не пойду что вы хотите считать, потому что нельзя групировать по тому что считашь, иначе тупо будут получаться еденицы - и это нормально. Если надо получить число отличное от ноль и один (ноль получится только если s.name попадется null такие могут быть, могут не быть в вашей БД) - то нужно групировать по другим полям, которые имеют концептуальное значение группа. Если у вас всегда еденица - то можно так и записать (схематически) select group1, 1 as cnt from table1 group by group1. Я вижу выборку примерно так (схематически)

select [подразделение],[направление], count (*) from ваша_таблица
/*Ваши условия 1*/
group by  [подразделение],[направление]
/*Ваши условия 2  having  и т п*/
order by 1,2

Возможно это будет так

select z.abbreviation, s.name dir, count(distinct(x.id)) cnt 
from employees x
left join staff y on x.id_e=y.id_e
left join division z on z.id_d=y.id_d
left join scientific_degree l on x.id_e=l.id_e
left join scientific_direction s on s.id_direction = l.id_direction
group by 
   z.abbreviation, s.name    
-- Далее хвост, не могу сказать будет ли этот хвост дружить с головой, отдельно сработает но прийдется добавлять условия что б отсечь лишнее
having count (l.id_direction) > 0 and l.d_start in (select max(sd.d_start)
from scientific_degree sd
where sd.d_start = l.d_start)
order by z.abbreviation,cnt desc;

Теперь про distinct. Обычно distinct хорошо работает, но есть ограничение. order by можно делать только по полям которые при select distinct, в некоторых версиях sql можно по "прямым выражениям" от данных полей. Т.е

select distinct    decode(grouping(s.name),1,'все_направления',s.name) as направление,
count(distinct(s.name)) as cnt
from employees x
left join staff y on x.id_e=y.id_e
left join division z on z.id_d=y.id_d
left join scientific_degree l on x.id_e=l.id_e
left join scientific_direction s on s.id_direction = l.id_direction
group by rollup(z.abbreviation,s.name,l.d_start)
having count (l.id_direction) > 0 and l.d_start in (select max(sd.d_start)
from scientific_degree sd
where sd.d_start = l.d_start)
/*вариант 1*/ order by направление -- если можно в вашем sql по алиасу 
/*вариант 2*/ order by 1 -- если можно в sql по номеру поля
/*вариант 3*/ order by decode(grouping(s.name),1,'все_направления',s.name) -- если можно в вашем sql по выражению

К сожалению разные версии sql по разному разрешают запись в order by, поэтому попробуйте по одному из трех вариантов.

select P1,P2,P3,P4 
   from Table1
 where (P1,P2) in (select P1,P2 
                            from Table1