Oracle 23ai: Advanced Analytics – Mastering Time Intervals and GROUP BY

Introduction

For many years, writing SQL meant dealing with rules that often felt unnecessary. Developers followed them because that was the only way things worked, even when it made queries longer or harder to read. With Oracle Database 23ai, things are starting to improve. Oracle has added new features that make SQL easier to write and easier to understand. In this blog, I’ll cover three of those improvements: aggregating interval data types, using column aliases in GROUP BY, and grouping by column position. These changes may seem small, but they can make a big difference in everyday SQL work.

Interval Data Type Aggregation

Working with time durations in SQL has always been awkward. Storing an interval was never the problem; the trouble started when you wanted to do something useful with it. Simple questions like “What’s the total time spent?” or “What’s the average duration?” often meant converting intervals into seconds or minutes, running the math, and then converting everything back again. It worked, but it wasn’t pretty, and it definitely wasn’t intuitive.

Oracle Database 23ai finally fixes this. Intervals can now be used directly with aggregate functions like SUM and AVG. This means you can work with durations as durations, without going through manual conversions. The database handles the math for you and returns a proper interval result.

Example:

In this example, we’ll look at a support system that tracks how long each ticket takes to resolve. The resolution time is stored using the INTERVAL DAY TO SECOND data type, which is a very natural way to represent durations.

First, we create a table and insert a few sample tickets with different resolution times:

-- Setup: Create a table with interval data
CREATE TABLE support_tickets (
    ticket_id NUMBER,
    resolution_time INTERVAL DAY TO SECOND
);

INSERT INTO support_tickets VALUES (1, INTERVAL '0 02:30:00' DAY TO SECOND);
INSERT INTO support_tickets VALUES (2, INTERVAL '0 01:15:00' DAY TO SECOND);
INSERT INTO support_tickets VALUES (3, INTERVAL '1 04:45:00' DAY TO SECOND);

Each row represents how long a ticket took to close, ranging from just over an hour to more than a full day.

Now, using Oracle 23ai, we can calculate the total and average resolution time directly:

-- Oracle 23ai: Direct aggregation
SELECT 
    SUM(resolution_time) AS total_time,
    AVG(resolution_time) AS avg_time
FROM support_tickets;

Before Oracle 23ai, this query would fail because SUM and AVG did not support interval data types. Developers had to convert intervals into numbers, perform the calculation, and then manually format the result back into a readable duration. In 23ai, Oracle handles this natively. The database understands how to add and average intervals and returns the result as a properly formatted INTERVAL DAY TO SECOND.

GROUP BY Alias

Repeating long expressions in the GROUP BY clause has always been one of Oracle SQL’s biggest annoyances. If you used a CASE expression or formatted value in the SELECT, you had to copy it exactly into the GROUP BY.

Oracle 23ai finally fixes this by allowing you to reference column aliases directly. This makes queries shorter, clearer, and much easier to maintain.

Example:

In this example, employees are grouped into salary bands using a CASE expression. Instead of repeating the entire expression in GROUP BY, I simply reuse the alias salary_level.

SELECT 
    CASE WHEN salary > 10000 THEN 'High' ELSE 'Standard' END AS salary_level,
    COUNT(*) AS total_staff
FROM employees
GROUP BY salary_level;

Group by Position

When you write GROUP BY queries, you often end up repeating the same columns or expressions that already appear in the SELECT list. This can get annoying, especially when those expressions are long or hard to read. Oracle Database 23ai makes this easier by allowing you to group by the position of columns instead of typing them again.

This works similarly to ORDER BY 1, 2. Instead of repeating column names or functions, you can simply refer to where they appear in the SELECT list. While it’s still a good idea to use column names in long-term or production code.

Example:

Since this is a new feature, it needs to be turned on for your session. If you don’t enable it, Oracle will treat the numbers as literal values and raise an error.

 -- Step 1: Enable the feature for your current session
ALTER SESSION SET group_by_position_enabled = TRUE;

NOTE: If you didn’t enable it, you will encounter the following error

Now let’s look at a simple sales example. We want to group sales data by region and by year, and then calculate the total sales amount.

-- Step 2: Group sales data using column positions
SELECT 
    region_name, 
    EXTRACT(YEAR FROM sale_date) AS sale_year,
    SUM(amount) AS total_sales
FROM (
    SELECT 'North' AS region_name, DATE '2023-01-01' AS sale_date, 100 AS amount FROM dual UNION ALL
    SELECT 'South' AS region_name, DATE '2023-05-15' AS sale_date, 250 AS amount FROM dual
)
GROUP BY 1, 2;

In this query, GROUP BY 1, 2 tells Oracle to group the results using the first and second columns from the SELECT list. The first column is region_name, and the second column is the year extracted from sale_date.

Conclusion

Oracle 23ai’s SQL enhancements show a welcome shift toward practicality and developer comfort. Being able to aggregate interval data directly, group by aliases, and group by position removes a lot of unnecessary repetition that used to clutter otherwise simple queries. The result isn’t just shorter SQL—it’s SQL that’s easier to read, reason about, and maintain over time. These features make refactoring older queries genuinely worthwhile, not just cosmetic. If you’re working with Oracle 23ai, it’s well worth revisiting existing SQL and taking advantage of these small but meaningful improvements.