Difference between collect and count

[Former Member]

Hi all,

Can you tell me the difference between collect statement and count statement for adding the records.

if so give me one example.

Please explain in detailed

thanks&regards

venkat

[Former Member]

COLLECT wa INTO itab [result].

Effect

This statement inserts the contents of a work area wa either as single row into an internal table itab or adds the values of its numeric components to the corresponding values of existing rows with the same key. As of Release 6.10, you can use result to set a reference to the inserted or changed row in the form of a field symbol or data reference.

Prerequisite for the use of this statement is that wa is compatible with the row type of itab and all components that are not part of the table key must have a numeric data type (i, p, f).

DATA: BEGIN OF seats,

carrid TYPE sflight-carrid,

connid TYPE sflight-connid,

seatsocc TYPE sflight-seatsocc,

END OF seats.

DATA seats_tab LIKE HASHED TABLE OF seats

WITH UNIQUE KEY carrid connid.

SELECT carrid connid seatsocc

FROM sflight

INTO seats.

COLLECT seats INTO seats_tab.

ENDSELECT.

where as Count is not a Key word. we will declare a data field as COunt to use it to count number of records based on some condition.

Where as DB count (count in SELECT statements) give the number of records.

[former_member188827]

COUNT( DISTINCT fdescriptor ).

Effect

For the lines selected, returns the number of different values in the column specified by the field labelfdescriptor. The DISTINCT declaration is compulsory. NULL values are ignored in the calculation unless all the values in a column are equal to NULL. If they are, the result is 0.

COUNT( * ).

Effect

Returns the number of lines selected. If the SELECT command contains a GROUP-BY clause, the system returns the number of lines for each group. You can use COUNT( * ) instead of COUNT(*).

COLLECT

COLLECT wa INTO itab [result].

Effect

This statement inserts the contents of a work area wa either as single row into an internal table itab or adds the values of its numeric components to the corresponding values of existing rows with the same key. As of Release 6.10, you can use result to set a reference to the inserted or changed row in the form of a field symbol or data reference.

Prerequisite for the use of this statement is that wa is compatible with the row type of itab and all components that are not part of the table key must have a numeric data type (i, p, f).

In standard tables that are only filled using COLLECT, the entry is determined by a temporarily created hash administration. The workload is independent of the number of entries in the table. The hash administration is temporary and is generally invalidated when the table is accessed for changing. If further COLLECT statements are entered after an invalidation, a linear search of all table rows is performed. The workload for this search increases in a linear fashion in relation to the number of entries.

In sorted tables, the entry is determined using a binary search. The workload has a logarithmic relationship to the number of entries in the table.

In hashed tables, the entry is determined using the hash administration of the table and is always independent of the number of table entries.

If no line is found with an identical key, a row is inserted as described below, and filled with the content of wa:

In standard tables the line is appended.

In sorted tables, the new line is inserted in the sort sequence of the internal table according to its key values, and the table index of subsequent rows is increased by 1.

In hashed tables, the new row is inserted into the internal table by the hash administration, according to its key values.

If the internal table already contains one or more rows with an identical key, those values of the components of work area wa that are not part of the key, are added to the corresponding components of the uppermost existing row (in the case of index tables, this is the row with the lowest table index).

The COLLECT statement sets sy-tabix to the table index of the inserted or existing row, in the case of standard tables and sorted tables, and to the value 0 in the case of hashed tables.

Outside of classes, you can omit wa INTO if the internal table has an identically-named header line itab. The statement then implicitly uses the header line as the work area.

COLLECT should only be used if you want to create an internal table that is genuinely unique or compressed. In this case, COLLECT can greatly benefit performance. If uniqueness or compression are not required, or the uniqueness is guaranteed for other reasons, the INSERT statement should be used instead.

The use of COLLECT for standard tables is obsolete. COLLECT should primarily be used for hashed tables, as these have a unique table key and a stable hash administration.

If a standard table is filled using COLLECT, it should not be edited using any other statement with the exception of MODIFY. If the latter is used with the addition TRANSPORTING, you must ensure that no key fields are changed. This is the only way to guarantee that the table entries are always unique and compressed, and that the COLLECT statement functions correctly and benefits performance. The function module ABL_TABLE_HASH_STATE can be used to check whether a standard table is suitable for editing using COLLECT.

[Former Member]

Collect is will add up the numeric values based on the other fields,

where as count is used along with select to find the no of records not the actual records

[Former Member]

hi,

collect will do the summation of all numeric fields, whereas count gives you the number of records for given selection.

reward points if useful..

[Former Member]

Hi,

Collect statement sums up all the values in a record if they are numeric n if u ahve any character fields n those records are treated as a separate one.

see this example.

DATA: BEGIN OF seats,

carrid TYPE sflight-carrid,

connid TYPE sflight-connid,

seatsocc TYPE sflight-seatsocc,

END OF seats.

DATA seats_tab LIKE HASHED TABLE OF seats

WITH UNIQUE KEY carrid connid.

SELECT carrid connid seatsocc

FROM sflight

INTO seats.

COLLECT seats INTO seats_tab.

ENDSELECT.

Reward if helpful.

Regards,

Harini.S

[Former Member]

Hi venkat v G murali mohan M

COLLECT is used to summate entries in an internal table:

COLLECT <wa> INTO <itab>.

<itab> must have a flat line type, and all of the fields that are not part of the table key must have a numeric type (F, I, or P). You specify the line that you want to add in a work area that is compatible with the line type.

When the line is inserted, the system checks whether there is already a table entry that matches the key. If there is no corresponding entry already in the table, the COLLECT statement has the same effect as inserting the new line. If an entry with the same key already exists, the COLLECT statement does not append a new line, but adds the contents of the numeric fields in the work area to the contents of the numeric fields in the existing entry.

You should only use the COLLECT statement if you want to create summarized tables. If you use other statements to insert table entries, you may end up with duplicate entries.

Lines are added to internal tables as follows:

Standard tables

If the COLLECT statement is the first statement to fill the standard table, the system creates a temporary hash administration that identifies existing entries in the table. The hash administration is retained until another statement changes the contents of key fields or changes the sequence of the lines in the internal table. After this, the system finds existing entries using a linear search. The runtime for this operation increases in linear relation to the number of existing table entries. The system field SY-TABIX contains the index of the line inserted or modified in the COLLECT statement.

Sorted tables

The system uses a binary search to locate existing lines. The runtime for the operation increases logarithmically with the number of existing lines. The system field SY-TABIX contains the index of the line inserted or modified in the COLLECT statement.

Hashed tables

The system finds existing lines using the hash algorithm of the internal table. After the COLLECT statement, the system field SY-TABIX has the value 0, since hashed tables have no linear index.

Example

DATA: BEGIN OF LINE,

COL1(3) TYPE C,

COL2(2) TYPE N,

COL3 TYPE I,

END OF LINE.

DATA ITAB LIKE SORTED TABLE OF LINE

WITH NON-UNIQUE KEY COL1 COL2.

LINE-COL1 = 'abc'. LINE-COL2 = '12'. LINE-COL3 = 3.

COLLECT LINE INTO ITAB.

WRITE / SY-TABIX.

LINE-COL1 = 'def'. LINE-COL2 = '34'. LINE-COL3 = 5.

COLLECT LINE INTO ITAB.

WRITE / SY-TABIX.

LINE-COL1 = 'abc'. LINE-COL2 = '12'. LINE-COL3 = 7.

COLLECT LINE INTO ITAB.

WRITE / SY-TABIX.

LOOP AT ITAB INTO LINE.

WRITE: / LINE-COL1, LINE-COL2, LINE-COL3.

ENDLOOP.

The output is:

1

2

1

abc 12 10

def 34 5

The example fills a sorted table. The first two COLLECT statements work like normal insertion statements. In the third COLLECT statement, the first line of ITAB is modified.

COLLECT statement

http://www.sts.tu-harburg.de/teaching/sap_r3/ABAP4/collect.htm

COUNT:

COUNT Returns the number of different values in the column determined by the database field f for the selected lines.

COUNT( * ) Returns the number of selected lines. If the SELECT command contains a GROUP BY clause , it returns the number of lines for each group. The form COUNT(*) is also allowed.

If ai is a field f , MAX( f ) , MIN( f ) or SUM( f ) , the corresponding column of the result set has the same ABAP/4 Dictionary format as f . With COUNT( f ) or COUNT( * ) , the column has the type INT4 , with AVG( f ) the type FLTP .

Performance

Specifying aggregate functions excludes the use of SAP buffering .

Since many database systems do not manage the number of table lines and therefore have to retrieve this at some cost, the function COUNT( * ) is not suitable for checking whether a table contains a line or not.

Output the number of airline carriers which fly to New York:

TABLES SPFLI.

DATA COUNT TYPE I.

SELECT COUNT( DISTINCT CARRID )

INTO COUNT FROM SPFLI

WHERE

CITYTO = 'NEW YORK'.

WRITE: / COUNT.

cheers!

gyanaraj

****Pls reward points if u find this helpful