ABAP: How to combine select max and count?

Question

Hello, I've got this:

select ordernr 
from users 
having count(ordernr) = 
( select max(count(ordernr)) 
    from users where ordernr = ordernr 
    group by ordernr ) 
group by ordernr

to get the most used order-number (ordernr) from all users.

How to get it into ABAP SAP System? I've tried this:

select SINGLE ordernr 
from ZDEVXXX_PROJECT3 INTO ordernrU 
having count( * ) = 
( select max( count( * ) ) 
    from ZDEVXXX_PROJECT3 
    where ordernr = ordernr 
    group by ordernr )

But I get this error:

"Unknown columnname COUNT("

How to combine max and count in ABAP? The SQL Query above is working in Oracle for me. Thanks!

Answer 1

You need to have COUNT(*) in the result set if you want to use it in the HAVING clause. See http://help.sap.com/abapdocu_70/en/ABENWHERE_LOGEXP_ALL_ANY_SOME.htm for an example.

Answer 2

Since release 6.1 you can use aggregates in HAVING clause. But your answer is "No way". Aggregates must be only in form aggr( {[distinct] column | *} ) So you must to

select count( * )
    into table itab
    from ZDEVXXX_PROJECT3 
    where ordernr = ordernr 
    group by ordernr

Then to find maximum of counts programmaticaly. And only then to use it in HAVING condition.

Answer 3

Or you can use ABAP Open SQL. It gives you the access to SQL of your particular DB and you can execute your mentioned above query.