ORA-00937: not a single-group group function Workaround-CodePudding

select
        o.customer_id,
        c.cust_first_name,
        c.cust_last_name,
        c.income_level,
        to_char(o.order_date, 'DD-MON-YY HH12:MI') as order_date,
        ROW_NUMBER() over (partition by o.customer_id order by o.order_date) as order#,
        o.order_total,
        lag(o.order_total, 1, 0) over (partition by o.customer_id order by o.customer_id) 
          lag(o.order_total, 2, 0) over (partition by o.customer_id order by o.customer_id) as last_two_orders_sum,
        min(o.order_date) keep (dense_rank last order by o.customer_id) as first_order_total
from orders o, customers c
where o.customer_id = c.customer_id

This is my query which produces this error:

ORA-00937: not a single-group group function

Expected result for the last column - first order of every customer: https://i.stack.imgur.com/kDWvZ.png

I know that I have to use GROUP BY clause to handle this error, but it seems rather inappropriate to group by all the other columns.. I know that this is a limitation in Oracle 11g and this issue is fixed in 12c, but I still need some kind of workaround.

Tried:

CTEs --> kinda weird to have single select in a CTE

Subquery --> can't figure out the right syntax and it produces a mess

Any help is appreciated!

CodePudding user response：

Rather than the MIN(...) KEEP (...) aggregation function, you want to use MIN(...) KEEP (...) OVER (...) analytic function or, possibly without the KEEP, use MIN(...) OVER (PARTITION BY ...):

select
        o.customer_id,
        c.cust_first_name,
        c.cust_last_name,
        c.income_level,
        to_char(o.order_date, 'DD-MON-YY HH12:MI') as order_date,
        ROW_NUMBER() over (partition by o.customer_id order by o.order_date)
          as order#,
        o.order_total,
        lag(o.order_total, 1, 0)
          over (partition by o.customer_id order by o.customer_id) 
          lag(o.order_total, 2, 0)
          over (partition by o.customer_id order by o.customer_id)
          as last_two_orders_sum,
        MIN(o.order_date) OVER (PARTITION BY o.customer_id) as first_order_date,
        MIN(o.order_total) KEEP (DENSE_RANK FIRST ORDER BY o.order_date)
                           OVER (PARTITION BY o.customer_id) as first_order_total
from    orders o
        INNER JOIN customers c
        ON o.customer_id = c.customer_id

Note: Also, use ANSI join syntax rather than the legacy comma-join syntax.

I know that this is a limitation in Oracle 11g and this issue is fixed in 12c, but I still need some kind of workaround.

No, this is not something that you fix by upgrading to a later version. It is a fundamental issue with your query that you are using an mix of aggregated and non-aggregated columns and, as the error message implies, you do not have a GROUP BY clause that includes all of the non-aggregated columns.

The solution is to not use any aggregation functions and change to only use analytic functions.

db<>fiddle here