topic SAP Query - Count(*) in Application Development and Automation Discussions

SAP Query - Count(*)

Sharadha1 — Mon, 08 Dec 2008 16:57:17 GMT

Hi,

i have a table with records

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

def 3

jkh 1

fgt 2

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

Regards,

Sharadha

Re: SAP Query - Count(*)

Former Member — Mon, 08 Dec 2008 17:03:15 GMT

Try this


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

This should remove all duplicate lines.

Re: SAP Query - Count(*)

Sharadha1 — Mon, 08 Dec 2008 17:21:14 GMT

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

Regards,

Sharadh

Re: SAP Query - Count(*)

Former Member — Mon, 08 Dec 2008 17:29:25 GMT

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

Re: SAP Query - Count(*)

Sharadha1 — Mon, 08 Dec 2008 17:35:36 GMT

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

Re: SAP Query - Count(*)

Former Member — Mon, 08 Dec 2008 17:44:05 GMT

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

Re: SAP Query - Count(*)

Former Member — Mon, 08 Dec 2008 18:01:14 GMT

Never did a Query.

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

Re: SAP Query - Count(*)

Sharadha1 — Mon, 08 Dec 2008 18:04:11 GMT

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

Re: SAP Query - Count(*)

monalisa_biswal — Mon, 08 Dec 2008 18:18:08 GMT

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

Re: SAP Query - Count(*)

Sharadha1 — Mon, 08 Dec 2008 18:23:06 GMT

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

Re: SAP Query - Count(*)

monalisa_biswal — Mon, 08 Dec 2008 19:34:13 GMT

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

Re: SAP Query - Count(*)

Sharadha1 — Tue, 09 Dec 2008 09:27:01 GMT

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

Re: SAP Query - Count(*)

Former Member — Tue, 04 Dec 2012 10:51:59 GMT

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