开发者

How to combine select max and count?

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 c开发者_JS百科ount( * ) = 
( 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!


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_751/en/ABENWHERE_LOGEXP_ALL_ANY_SOME.htm for an example.


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.


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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜