ROW_NUMBER() syntax-CodePudding

cte3 as 
    (select
        customer_id,        
        count(order_id) as total_orders,
        ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_id) AS order_num
    from orders
    group by customer_id)

I'm new to SQL on Oracle. I have a problem with the ROW_NUMBER() analytical function and its syntax. What I want is to show every order from every customer like so:

order_id    cust_id  total_orders     order_num
128128      1        2                1
256256      1        2                2
512512      2        1                1

When I try to run the query, it gives me:

ORA-00979: not a GROUP BY expression

I did a little research on the error and tried this:

group by customer_id, order_id`` --> gives me correct row_num` column, but duplicate results and wrong
group by customer_id/order_id --> doesn't work, as it raises ORA-00979

CodePudding user response：

This will work. For more help we need more data from you.

select customer_id,        
       count(order_id) as total_orders,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_id)
from orders
group by customer_id, order_id

Every column that is not a part of aggregate function needs to be in a group by expression

I can just suggest one of the solutions(for what I believe you need):

First option is to do a join with a subquery.

select o1.order_id, 
       o1.customer_id,        
       o3.total_orders,
       ROW_NUMBER() OVER (PARTITION BY o1.customer_id ORDER BY o1.order_id)
from orders o1
left join (select count(o2.order_id) as total_orders
                  , o2.customer_id as ci
           from orders o2 
           group by customer_id) o3 on o1.customer_id = o3.ci

OR:

Second option is to use count analytic function:

select order_id, 
       customer_id,        
       count(order_id) over (partition by customer_id) as total_orders,
       ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_id)
from orders

Here is the demo