Let's break down why your query is throwing that error, then build the correct SQL to get the total time each order takes from its first step start to last step finish.

Why You’re Getting ORA-00937 #

The ORA-00937: not a single-group group function error happens because you’re nesting aggregate functions incorrectly. Your query uses MEDIAN(ABS(MIN(StepStart - StepFinish)))—Oracle doesn’t allow using one aggregate function (like MIN) directly inside another (like MEDIAN) in the same SELECT clause without proper subquery or window function structure.

Beyond the syntax issue, your logic also doesn’t align with your goal: you’re trying to calculate a median of minimum step time differences, not the total duration from the first step’s start to the last step’s end.

Correct Approach to Calculate Total Duration #

Your goal is, for each OrderNumber:

1. Grab the StepStart from the step with the smallest StepNumber (the first step)
2. Grab the StepFinish from the step with the largest StepNumber (the last step)
3. Subtract the start time from the finish time, then convert the result to days (divide by 24 since Oracle stores time differences in hours for DATE types)

Solution 1: Using Correlated Subqueries #

This approach uses subqueries to identify the first and last steps per order:

SELECT 
    OrderNumber,
    ROUND(
        (MAX(CASE WHEN StepNumber = (SELECT MAX(StepNumber) FROM orders o2 WHERE o2.OrderNumber = o1.OrderNumber) THEN StepFinish END) 
        - MIN(CASE WHEN StepNumber = (SELECT MIN(StepNumber) FROM orders o2 WHERE o2.OrderNumber = o1.OrderNumber) THEN StepStart END)) 
        / 24, 
        3
    ) AS TimetoFinish
FROM orders o1
GROUP BY OrderNumber;

The ROUND function ensures you get the decimal precision matching your expected results (0.62 for order 100, 0.3125 for order 200).

Solution 2: Using Window Functions (Cleaner & Faster) #

Window functions are more efficient for ranking steps per order:

WITH order_step_ranks AS (
    SELECT 
        OrderNumber,
        StepStart,
        StepFinish,
        -- Rank steps from first to last
        ROW_NUMBER() OVER (PARTITION BY OrderNumber ORDER BY StepNumber ASC) AS first_step_rank,
        ROW_NUMBER() OVER (PARTITION BY OrderNumber ORDER BY StepNumber DESC) AS last_step_rank
    FROM orders
)
SELECT 
    OrderNumber,
    ROUND(
        (MAX(CASE WHEN last_step_rank = 1 THEN StepFinish END) - MIN(CASE WHEN first_step_rank = 1 THEN StepStart END)) / 24,
        4
    ) AS TimetoFinish
FROM order_step_ranks
GROUP BY OrderNumber;

The CTE (order_step_ranks) assigns a rank to each step—first_step_rank = 1 marks the first step, last_step_rank = 1 marks the last. We then aggregate those values to calculate total duration.

Verifying the Results #

For order 100:

• First step start: 9:00 AM
• Last step finish: 11:55 PM
• Duration: 14 hours 55 minutes ≈ 14.9167 hours → 14.9167 / 24 ≈ 0.62 days

For order 200:

• First step start: 5:00 AM
• Last step finish: 12:30 PM
• Duration: 7.5 hours → 7.5 / 24 = 0.3125 days

Both match your expected output perfectly.

内容的提问来源于stack exchange，提问作者artemis