topic Re: Max Count in Application Development and Automation Discussions

Max Count

Former Member — Mon, 18 Jan 2010 16:12:07 GMT

Hi,

i'm a newbie in Abap-Development but I Have developed in some other languages.

My Problem is, that I have to select the max count of a table like this:

Select tab1-field1 max( Count( tab2-field1 ) ) from tab1 inner join tab2 on tab1-field1 = tab2-field2 group by tab1-field1

In single steps:

What I want to see, is the maximum count of tab2-field1 in relation to tab1-field1. How can I do this? In Abap I can not write this sql, because ofter the max is an field expected, am i right?

Thanks,

Peter

Re: Max Count

kesavadas_thekkillath — Mon, 18 Jan 2010 16:23:05 GMT

You cannot use MAX and COUNT together i think so.

Try this

Select tab2_{field2 max( tab2}field1 )

from tab2 inner join tab1

on tab2_{field2 = tab1}field1

group by tab2~field2.

Re: Max Count

Former Member — Mon, 18 Jan 2010 16:30:42 GMT

Hi,

this sql will not solve my problem. In this case I have the maximum value of the field, but not the maximum count.

A simple example:

Tab1:

Field1 Field2

1 2

2 3

Tab2

Field1 Field2

1 1

2 1

3 1

4 2

5 2

The result I will have

1 3

2 2

This is a simple example. in reality I must join over more than 3 tables.

Re: Max Count

Former Member — Mon, 18 Jan 2010 16:48:44 GMT

Peter, your requirement is not clear.

If you want to see both the rows

1 3

2 2

then why are you using max(count())?

Shouldn't you be using Keshav's suggestion, but replace max with count?

Or, do you just want to see one row (1 3) in your results?

If you want the results to be one row, then you can accomplish that using two SQL statements. I don't know if there is a way to do that in one statement.

select field2 count(field2)

into (l_field2, l_field2_count)

from tab2

up to 1 rows

group by field2

order by fiel2 descending.

select field1 field2

into (l_tab1_field1, l_tab1_field2)

from tab1

where field1 = l_field2.

Re: Max Count

Former Member — Mon, 18 Jan 2010 17:09:35 GMT

If you can do this in the SQL of the database, tyhen you should be able to do it in ABAP native rather than open SQL.You can learn about native SQL by pressing F1 on EXEC

Rob

Re: Max Count

Former Member — Mon, 18 Jan 2010 17:09:43 GMT

Okay... i will explain again:

in table1 i have some vendors and in table2 are some vouchers to different vendors with different dates.

If I select the count of vouchers differentiated by month and vendor, i want the maximum of this result differentiated by the vendor.

So I will have the maximum count of voucher in a month per vendor. For this I must first select the count of vouchers per month and then select the maximum of this result for each vendor.

Sorry, I don't know how to explain better.

Re: Max Count

kesavadas_thekkillath — Mon, 18 Jan 2010 18:20:14 GMT

Select the data to your internal table and then manipulate it.

Re: Max Count

Former Member — Tue, 19 Jan 2010 08:23:15 GMT

Hi..

it's not right, that I con do the same SQL-Statement in Open-SQL than in "normal"-SQL. In Open-SQL I can not do something like this:

Select a_{field1 b}field2 From tab1 as a (Select * from tab2) as b

This SQL-Statement I can procced direct on all databases but not in Open-SQL. Or?

Greets,

Peter

Re: Max Count

Former Member — Tue, 19 Jan 2010 08:40:09 GMT

Hi Peter,

I can understand your concern. for all these kind of logic eg. Max, Min, second maximum etc, - These are ok if we directly in sql in database.(non-sap) eg. Oracle, MS SQL etc, in legacy systems.

But in SAP, in abap, such complicated queries are not feasible. For such things, it is better to manipulate internal tables and use abap syntax to find out the desire result.

In your case, we can use COLLECT syntax (please see F1 help) to get the SUMMARY of records,

Vendor wise + Month wise to get the COUNT.

Then you can sort this itab DESCENDING to get the max.

...... this kind of some logic, instead of TWISTING the SQL.

hope this helps.

regards,

amit m.