Oracle Database 23ai introduces GROUP BY ALL, a small SQL feature that removes the need to manually list columns in the GROUP BY clause.

Lets create some sample data for test

--create table

CREATE TABLE test_tbl
(
  id       NUMBER,
  job_name VARCHAR2(32),
  salary   NUMBER
);

--add data

INSERT INTO test_tbl VALUES (10, 'DEV', 5000);
INSERT INTO test_tbl VALUES (10, 'DEV', 6000);
INSERT INTO test_tbl VALUES (10, 'DBA', 7000);
INSERT INTO test_tbl VALUES (20, 'DEV', 5500);
INSERT INTO test_tbl VALUES (20, 'DBA', 8000);

COMMIT;

Classic group by expects all columns to be defined in group by clause

SELECT id,
job_name,
SUM(salary) AS total_salary
FROM test_tbl
GROUP BY id, job_name;

ID  JOB_NAME  TOTAL_SALARY
--- --------- ------------
10  DEV       11000
10  DBA       7000
20  DEV       5500
20  DBA       8000

New option is much simpler no need to do that. I love it !

SELECT id,
job_name,
SUM(salary) AS total_salary
FROM test_tbl
GROUP BY ALL;

 ID JOB_NAME  TOTAL_SALARY
--- --------- ------------
10  DEV       11000
10  DBA       7000
20  DEV       5500
20  DBA       8000

--no need to specify column aliases
SELECT id,
job_name,
SUM(salary)
FROM test_tbl
GROUP BY ALL;

 ID JOB_NAME  SUM(SALARY)
--- --------- -----------
10  DEV       11000
10  DBA       7000
20  DEV       5500
20  DBA       8000 

Have fun 🙂

  Tomasz