Sort aggregated query results by two methods simultaneously

Window functions are allowed in order by, even if you're aggregating. _{demo at db<>fiddle}

ORDER BY/*1*/least(4,row_number()over(order by count(e.employeetypeid)desc)),
        /*2*/id

• row_number()over(order by count(..)) scores rows by prevalence in another table
• least(4,row_number(..)) makes sure the top 3 get positions 1,2,3, the rest fights for 4
• among 4's, alphabetical order of id decides as the 2nd order by expression

Quoting the doc:

Window functions are permitted only in the SELECT list and the ORDER BY clause of the query. They are forbidden elsewhere, such as in GROUP BY, HAVING and WHERE clauses. This is because they logically execute after the processing of those clauses. Also, window functions execute after non-window aggregate functions. This means it is valid to include an aggregate function call in the arguments of a window function, but not vice versa.

You can clean things up a bit with a named window clause (also in MySQL, SQLite, Trino, SQL Server 2022 (16+):

SELECT et.id,
       et.employeetype_description,
       count(e.employeetypeid) as thesortorder
FROM employeetype et
LEFT JOIN employee e ON e.employeetypeid = et.id
GROUP BY et.id,
         et.employeetype_description
WINDOW w1 AS (order by count(e.employeetypeid) desc)
ORDER BY /*1*/ least((row_number() over w1), 4),
         /*2*/ id

In this method, the additional tie-breaking @ThorstenKettner's comment asks to specify, simply goes into the window spec:

• To use count(*) for placing more than 3 rows on top in case their counts draw:

ORDER BY least(4,dense_rank()over(order by count(e.employeetypeid)desc)),id

• To use alphabetical sorting to break ties on top 3 positions:

ORDER BY least(4,row_number()over(order by count(e.employeetypeid)desc, id)),id

Calculate the ordering with a CASE on analytics in a subquery:

select id, employeetype_description, cnt
from (
    select id, employeetype_description, cnt,
        case when row_number() over(order by cnt desc) <= 3
            then row_number() over(order by cnt desc)
            else row_number() over(order by employeetype_description asc) + 3
        end as rn
    from (
        SELECT 
            et.id,
            et.employeetype_description,
            count(e.employeetypeid) as cnt
        FROM 
            employeetype et
        LEFT JOIN
            employee e ON e.employeetypeid = et.id
        GROUP BY
            et.id,
            et.employeetype_description
        )
)
order by rn
;

(typed in the browser, may contain typos, since no DDL provided...)

Subqueries in the ORDER BY

Generally speaking, you can use complex expression, and notably subqueries in the ORDER BY.

Here it would allow you to condition your first ORDER BY term to the id being one of your 3 most populated employee types:

SELECT 
    et.id,
    et.employeetype_description,
    count(e.employeetypeid) as thesortorder
FROM 
    employeetype et
LEFT JOIN
    employee e ON e.employeetypeid = et.id
GROUP BY
    et.id,
    et.employeetype_description
ORDER BY
    CASE WHEN et.id IN
    (
        SELECT employeetypeid
        FROM employee
        GROUP BY 1
        ORDER BY COUNT(*) DESC
        LIMIT 3
    )
    THEN count(e.employeetypeid) END
    DESC NULLS LAST,
    employeetype_description
;

Or use a correlated subquery:

WITH top3 AS
(
    SELECT employeetypeid AS id, COUNT(*) count
    FROM employee
    GROUP BY 1
    ORDER BY 2 DESC LIMIT 3
)
SELECT 
    et.id,
    et.employeetype_description,
    count(e.employeetypeid) as thesortorder
FROM 
    employeetype et
LEFT JOIN
    employee e ON e.employeetypeid = et.id
GROUP BY
    et.id,
    et.employeetype_description
ORDER BY
    (SELECT count FROM top3 WHERE top3.id = et.id) DESC NULLS LAST,
    employeetype_description
;

But as you notice, we obtain something quite complex and, moreover, repetitive (COUNT() is computed twice).
And as you now, in any programming language as well as in data models, duplication is risky…

row_number() window function

… So in your specific case where thesortorder is used not only for the ORDER BY but also to be a result column,
I would first compute the COUNT(*) for every row of the desired resultset in a Common Table Expression
(with "Common" meaning we want to reuse it: once for its data, once as a sort criteria, we're good to go),
with an additional column telling where each row is placed compared to the others, thanks to the row_number() window function.
Then condition the use of the "count" sort criteria, to this position being computed as one of the first 3 rows of this CTE.

WITH e AS
(
    SELECT
        employeetypeid,
        COUNT(*) count,
        ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) pos
    FROM employee
    GROUP BY 1
)
SELECT 
    et.id,
    et.employeetype_description,
    e.count as thesortorder
FROM 
    employeetype et
LEFT JOIN
    e ON e.employeetypeid = et.id
ORDER BY
    CASE WHEN e.pos <= 3 THEN e.count END DESC NULLS LAST,
    employeetype_description
;

The 3 methods are shown, in that order, in a db<>fiddle (where I gave different counts to C, D and E, to avoid a false positive due to incidentally already sorted data):

Final note: the richness of window functions offers a lot of flexibility, take time to read their list to get a glimpse;
for example, you could use rank() instead of row_number() to address the problem of ties that Thorsten Kettner pointed.
Or use percent_rank().
Even look for the "best placed cut" by finding the biggest gap between 2 successive counts, so that if you had 20, 10, 9, 8, 2, 1, 1 you took the 4th (8) with the first three.

Use a CTE to calculate the result set and use it twice:

WITH q AS (
   SELECT empl_type, count,
   lag(count, 3) OVER (ORDER BY count) IS NULL AS leading_three
   FROM ...
)
(SELECT empl_type FROM q WHERE leading_three ORDER BY count DESC)
UNION ALL
(SELECT empl_type FROM q WHERE NOT leading_three ORDER BY empl_type);

That solution depends on PostgreSQL's implementation of UNION ALL, which just appends the two result sets. There is nothing in SQL that forces the database to produce the result of UNION ALL in a specific order. So only use my solution if you don't mind depending on an implementation detail that might change in the future, even if that is unlikely.

For a solution that does not depend on the implementation of PostgreSQL, run two separate queries and append the result sets on the client.