SAP Query - Count(*)

[Sharadha1]

Hi,

i have a table with records

abc

abc

def

fgt

def

abc

fgt

abc

def

jkh

i want the SAP query to display as

abc - 4

def - 3

jkh - 1

fgt - 2

when i wrote a query ( select count(*) ) in infoset and added an additional field count, it is giving me the right count but it is repeated as many times as the record, like below

abc 4

abc 4

abc 4

abc 4

def 3

def 3

def 3

jkh 1

fgt 2

fgt 2

Can you please tell me how to avoid this multiple lines?

Regards,

Sharadha

[Former Member]

Try this

SORT your_table BY field1 field2.
DELETE ADJACENT DUPLICATES FROM your_table COMPARING field1 field2.

This should remove all duplicate lines.

[Sharadha1]

i don think i can do this inside SAP QUERY..i am using SQ01..query for reporting

Regards,

Sharadh

[Former Member]

Hi,

Try coding the "record processing" event in the code section...

check the internal table using READ TABLE statement to check if the record is already added...if the record is already use a check continue processing the next record.

Thanks

Naren

[Sharadha1]

i am using the table ltap in the SAP query report.

so in record processing, when i gave

sort ltap[]

it gives error that "LTAP" is not an internal table "OCCURS n" specification is missing.

any other ideas?

Regards,

Sharadha

[Former Member]

Hi,

I think LTAP is a field group and not an internal table...

You can create your own internal table in the DATA section...and add the entries during the RECORD_PROCESSING event..

Ex..

****DATA Section
TYPES: BEGIN OF type_data,
               value TYPE char10,
             END OF type_data.
DATA: MY_ITAB TYPE STANDARD TABLE OF type_data,
           MY_WA   TYPE type_data.

****In RECORD_PROCESSING section.

READ TABLE MY_ITAB TRANSPORTING NO FIELDS
                     WITH KEY value = LTAP-value.

IF sy-subrc = 0.
  CHECK 1 = 0.        " Don't proceed with the record as it is already added.

ELSE.   " REcord not added.

  MY_WA-value = LTAP-value.
  APPEND MY_WA TO MY_ITAB.
  
ENDIF.

Hope this is clear.

Thanks

Naren

[Sharadha1]

Thanks a lot Naren. I am doing this now.. but how to add it new internal table my_data to one of the field groups so that i can bring them on the report.

Regards,

Sharadha

[Former Member]

Never did a Query.

Can you use the GROUP BY adittion in a Query? that would solve the problem

[monalisa_biswal]

create another internal table with two fields fieldname, count

data: begin of itab,

fname type <same as your field>,

count type i,

end of itab.

select fname count(*) from <table> into table itab group by fname.

Hope this helps!

Mona

[Sharadha1]

Hi,

i have done this already..but now i am not able to bring this new internal table into the field group so that i can display it finally in the report.

regards,

Sharadha

[monalisa_biswal]

As I understood from your message you need count also to be displayed in the report.

So in your parent internal table you can add this field count to hold the number of lines.

now find count in another internal table.

append lines of <second internal table> to <parent internal table>.

sort <parent internal table> by <fname> <count> descending.

delete adjacent duplicates from <parent internal table>.

Hope this helps!

Monalisa

[Sharadha1]

Hi Mona,

I have done already what you have told. My problem is how to add this 'parent internal table' which is defined in the DATA section to a field group so that i can bring it up in the report fianlly.

can you pls help me with this.

Thanks,

Sharadha

[Former Member]

Hello Sharadha,

Have you found a solution for this issue or not ? i'm facing exactly the same problem if you can help me even if the post is very old.

Thanks by advance 🙂

Regards,

Soufiane