How to Use GROUP BY in Oracle Database 23ai

The GROUP BY clause in Oracle Database 23ai is used when you want to organize rows into groups and then apply aggregate functions such as COUNT, SUM, AVG, MIN, or MAX. Instead of returning every single row, GROUP BY allows you to summarize data so that you can see meaningful patterns. In this tutorial, you will learn how GROUP BY works in Oracle Database 23ai with practical examples, including its use with modern data types such as JSON and VECTOR.

Preparing sample data

Before we dive into GROUP BY, let’s create a set of tables that we will use for our examples. These tables include employees, departments, products, and documents.

-- Drop old tables if they exist
DROP TABLE employees PURGE;
DROP TABLE departments PURGE;
DROP TABLE products PURGE;
DROP TABLE documents PURGE;

-- Employees table
CREATE TABLE employees (
  employee_id   NUMBER PRIMARY KEY,
  first_name    VARCHAR2(50),
  last_name     VARCHAR2(50),
  hire_date     DATE,
  salary        NUMBER(10,2),
  department_id NUMBER,
  commission_pct NUMBER
);

INSERT INTO employees VALUES (1,'John','Smith',   DATE '2020-01-15', 8000, 10, NULL);
INSERT INTO employees VALUES (2,'Alice','Johnson',DATE '2019-03-10',12000, 20, 0.10);
INSERT INTO employees VALUES (3,'Michael','Brown',DATE '2021-06-20', 4500, 10, NULL);
INSERT INTO employees VALUES (4,'Sophia','Davis', DATE '2022-11-05', 6000, 30, 0.05);
INSERT INTO employees VALUES (5,'David','Wilson', DATE '2021-02-18', 9500, 20);

-- Departments table
CREATE TABLE departments (
  department_id NUMBER PRIMARY KEY,
  department_name VARCHAR2(100)
);
INSERT INTO departments VALUES (10,'Sales');
INSERT INTO departments VALUES (20,'HR');
INSERT INTO departments VALUES (30,'IT');

-- Products with JSON data
CREATE TABLE products (
  product_id   NUMBER PRIMARY KEY,
  product_data JSON
);

INSERT INTO products VALUES (1,'{"name":"Laptop","specs":{"cpu":"Intel i7","ram":"16GB"},"price":1299}');
INSERT INTO products VALUES (2,'{"name":"Phone","specs":{"cpu":"Snapdragon","ram":"8GB"},"price":699}');
INSERT INTO products VALUES (3,'{"name":"Tablet","specs":{"cpu":"M2","ram":"12GB"},"price":899}');
INSERT INTO products VALUES (4,'{"name":"Laptop","specs":{"cpu":"Intel i5","ram":"8GB"},"price":999}');
INSERT INTO products VALUES (5,'{"name":"Phone","specs":{"cpu":"Apple A16","ram":"6GB"},"price":1099}');

-- Documents with VECTOR embeddings
CREATE TABLE documents (
  doc_id    NUMBER PRIMARY KEY,
  title     VARCHAR2(200),
  embedding VECTOR
);

INSERT INTO documents VALUES (1,'Intro to 23ai','[0.10,0.80,0.50]');
INSERT INTO documents VALUES (2,'Write SQL Fast','[0.20,0.70,0.60]');
INSERT INTO documents VALUES (3,'AI and Databases','[0.85,0.15,0.20]');
COMMIT;

Now we have some data to practice with.

Basic GROUP BY in Oracle 23ai

The most common use of GROUP BY is to calculate totals by category. For example, if we want to see the total salary paid in each department:

SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id;

Result

Here, Oracle groups employees by department and adds up the salaries.

GROUP BY with COUNT

We can count how many employees each department has:

SELECT department_id, COUNT(*) AS num_employees
FROM employees
GROUP BY department_id;

Result

GROUP BY with Multiple Columns

You can group by more than one column. For example, group by both department and commission percentage:

SELECT department_id, commission_pct, COUNT(*) AS total_people
FROM employees
GROUP BY department_id, commission_pct;

Result

This shows how many employees in each department share the same commission percentage.

GROUP BY with HAVING

The HAVING clause filters groups after aggregation. For example, to show only departments with total salaries above 10000:

SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id
HAVING SUM(salary) > 10000;

Result

GROUPING SETS in Oracle 23ai

Oracle supports GROUPING SETS to calculate multiple groupings in one query. This is more efficient than running separate queries.

SELECT department_id, commission_pct, SUM(salary) AS total_salary
FROM employees
GROUP BY GROUPING SETS (
  (department_id),
  (commission_pct),
  ()
);

Result

This single query produces totals by department, by commission, and overall.

Filtering JSON with GROUP BY in Oracle 23ai

Since Oracle 23ai supports native JSON, you can group by values inside a JSON document. For example, group products by CPU type:

SELECT
  JSON_VALUE(product_data, '$.specs.cpu')                               AS cpu_type,
  COUNT(*)                                                               AS num_products,
  AVG(JSON_VALUE(product_data, '$.price' RETURNING NUMBER))             AS avg_price
FROM products
GROUP BY
  JSON_VALUE(product_data, '$.specs.cpu');

Result

This makes it easy to analyze unstructured JSON data without converting it.

Grouping and Analyzing Vector Search Results

While you cannot group directly on VECTOR values, you can compute similarity distances and group or filter results based on thresholds. For example, group documents by whether they are “highly similar” to a given vector.

SELECT CASE
         WHEN VECTOR_DISTANCE(embedding, '[0.18,0.72,0.58]', COSINE) < 0.3
              THEN 'Highly Similar'
         ELSE 'Less Similar'
       END AS similarity_group,
       COUNT(*) AS doc_count
FROM documents
GROUP BY CASE
           WHEN VECTOR_DISTANCE(embedding, '[0.18,0.72,0.58]', COSINE) < 0.3
                THEN 'Highly Similar'
           ELSE 'Less Similar'
         END;

Result

This shows how GROUP BY can be combined with vector search results to classify and count documents.

Best Practices for GROUP BY

Choose only the columns you really need in GROUP BY to avoid unnecessary computation. Use HAVING for filtering groups instead of WHERE. For JSON, always ensure paths are valid and match the data structure. When using vectors, group by derived categories rather than raw vector values. For large datasets, combine GROUP BY with indexes to keep performance high.

Conclusion

The GROUP BY clause in Oracle Database 23ai is essential for analyzing data. It lets you summarize numbers, count rows, apply conditions, and combine structured filtering with new data types like JSON and VECTOR. By mastering GROUP BY, you can quickly turn raw data into meaningful insights that support decision-making.

Note: All the examples in this tutorial were tested on Oracle Database 23ai using Oracle SQL Developer version 24.