Batch insertion into multiple tables based on a single result set

You simply need to control your transaction. When you do your first insert, you create a new transaction that does not auto-commit. It remains open. You can then do your second insert that does your aggregation. The transaction is still open - at any time you can roll it all back, or commit your work up to that point. There is no need to try to do it all in a single SQL statement. You only need to do it in a single transaction, which can have many SQL statements.

In PL/SQL:

BEGIN
  INSERT INTO table_A....;
 
  INSERT INTO table_B.... GROUP BY ....;

  COMMIT; -- will only commit work if both table_A and table_b succeeded
EXCEPTION
  WHEN OTHERS THEN
    -- report somehow, like dbms_output.put_line(SQLERRM) or log error in a log table
    ROLLBACK; -- undoes work on table_A if table_B failed.
END;

Of course, if you are using an ad-hoc SQL script in a database client software of some kind and not submitting a PL/SQL block like I've shown here, then you may have auto-commit configured in your software's options/settings/preferences (it's controlled by the client, not the database). The solution then is either to use PL/SQL like my example above, or to change your software not to auto-commit and handle the committing or rolling back yourself.

You could use INSERT ALL

INSERT ALL 
  INTO table1 (<cols to insert 1>) 
    VALUES (<cols from result>) 
  INTO table2 (<cols to insert 2>) 
    VALUES (< cols from result>) 
  WITH dat AS ( 
    < your data >
  ) 
  SELECT * FROM dat;

for reference.

You can use:

BEGIN
  INSERT INTO a (name, department, salary)
  SELECT name, department, salary
  FROM   (
    SELECT name, department, salary,
           SUM(salary) OVER (PARTITION BY department) AS dept_salary
    FROM   table_name
  )
  WHERE  dept_salary > 100;

  INSERT INTO b (department, salary)
  SELECT department, SUM(salary) FROM table_name GROUP BY department;

  COMMIT;
END;
/

Which, for the sample data:

CREATE TABLE table_name (name, department, salary) AS
SELECT 'Jason',   'Finance',     100 FROM DUAL UNION ALL
SELECT 'Bourne',  'Finance',     120 FROM DUAL UNION ALL
SELECT 'Michael', 'Finance',      90 FROM DUAL UNION ALL
SELECT 'Robert',  'Sales',        75 FROM DUAL UNION ALL
SELECT 'Downey',  'Sales',       500 FROM DUAL UNION ALL
SELECT 'Matthew', 'Engineering',  10 FROM DUAL UNION ALL
SELECT 'Tom',     'Engineering',  90 FROM DUAL;

CREATE TABLE a AS
SELECT * FROM table_name WHERE 1 = 0;

CREATE TABLE b (department, salary) AS
SELECT department, salary FROM table_name WHERE 1 = 0;

Then after the INSERTs, table A contains:

and table B contains:

fiddle