ERROR: Summary functions nested in this way are not supported.

[carihuppert]

 proc sql;

create table MostPopularRouterByState as

select c.State, o.SKU, count(o.OrderID) as TotalOrders

 from DATA_ELE.ORDERS o

 inner join DATA_ELE.CUSTOMERS c on o.CustomerID = c.CustomerID

 group by c.State, o.SKU

 having calculated TotalOrders = (select max(calculated TotalOrders) from DATA_ELE.ORDERS o2

 inner join DATA_ELE.CUSTOMERS c2 on o2.CustomerID = c2.CustomerID

77 where c.State = c2.State

78 group by c2.State, o2.SKU)

79 order by c.State, TotalOrders desc;

ERROR: Summary functions nested in this way are not supported.

[mkeintz]

Do you have a question?

[LinusH]

Your sub-query doesn't know about columns created in the outer query, so you need to replicate the logic from the outer query in your sub-query.

I don't know your requirement, but I would guess you wnat to have a GROUP BY in your outer query, or what is your desired en result?

[Kurt_Bremser]

Do you want the most sold sku for every state, or just the overall highest order count and the state/sku combination associated with it?