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.
精彩评论