Count duplicates in an internal table?

Question

I just want to ask on how to count duplicates in an internal table. I wanted to this in order for me to count per customer and put it into the Customer count column.

Sales Employee          Customer    Customer Count
a                          1             2
a                          2             2
b                          3             3
b                          2             3
b                          4             3
c                          1             1

Use [GROUP BY](https://help.sap.com/http.svc/rc/abapdocu_751_index_htm/7.51/en-US/abapgroupby_clause.htm) SQL clause and build itab already with aggregated results. It's more efficient than counting dups inside itab. — Suncatcher, Apr 25 '18 at 08:05

score 4 · Answer 1 · answered Apr 25 '18 at 08:12

as suncatcher mentions in his comment, using sql aggregates is more efficient than looping through internal tables. But if that is not possible in your case, one way would be to use the collect statement. collect adds entries to an internal table and adds up numerical fields when a row with the same key fields already exists. Create an internal table with a field for your sales employee, another field for the count and loop through your sales table, using collect to update your count table for each sale.

types: begin of t_count,
       employee type text10,
       count type i,
       end of t_count.

data: it_count type standard table of t_count,
      wa_count type t_count.

loop at it_sales into wa_sales.
    move: wa_sales-employee to wa_count-employee,
          1 to wa_count-count.

    collect wa_count into it_count.
endloop.

The example assumes you have a table it_sales, a work area wa_sales, both with a field employee. Table it_count then contains a list of your employees (in the order they appear in your sales table) and the number of times they appeared in the sales table.

You should change it_count to a sorted table. The documentation on COLLECT mentions that you should defer from using it on standard tables (performance). — user1498318, Apr 25 '18 at 14:43
Yeah I've tried it but i tried putting some changes into his logic that will meet my requirements. Anyways thanks! — gwapo, May 08 '18 at 00:17

score 1 · Answer 2 · answered May 10 '18 at 12:40

FIELD-SYMBOLS : <lfs_sales> TYPE ty_sales.

Assuming li_sales is an internal table with columns Sales_employee, Customer and customer_count. Initially the table entries are present as follows.

Sales_employee Customer customer_count
a                 1             0
a                 2             0
b                 3             0
b                 2             0
b                 4             0
c                 1             0

We need to calculate the duplicate sales_employee count and update the customer_count field. We can make use of collect statement as suggested by Dirik or make use of control break statements as shown below.

Prerequisite to make use of SUM keyword is to initialize the customer_count as 1 in each row so that it can sum up the customer count based on similar sales_employee.

LOOP AT li_sales ASSIGNING <lfs_sales>.
     <lfs_sales>-customer_count = 1.
ENDLOOP.

Now the entries look as shown below.

Sales_employee Customer customer_count
a                 1             1
a                 2             1
b                 3             1
b                 2             1
b                 4             1
c                 1             1

Following code does update the customer_count field value.

LOOP AT li_sales INTO rec_sales.
AT END OF employee.
  SUM.
  MOVE-CORRESPONDING rec_sales TO rec_count.
  APPEND rec_count TO li_count.
  CLEAR rec_count.
ENDAT.
ENDLOOP.

SORT li_count BY employee.
LOOP AT li_sales ASSIGNING <lfs_sales>.
  CLEAR rec_count.
  READ TABLE li_count INTO rec_count
  WITH KEY employee = <lfs_sales>-employee
  BINARY SEARCH.
  IF sy-subrc IS INITIAL.
    <lfs_sales>-count = rec_count-count.
  ENDIF.
ENDLOOP.

Now the internal table gets assigned with customer_count as below.

Sales_employee Customer customer_count
a                 1             2
a                 2             2
b                 3             3
b                 2             3
b                 4             3
c                 1             1

Count duplicates in an internal table?

2 Answers2