Combine selection and counting of values from database

[dschiener]

Hi there,

I am looking for a solution to combine the selection of values from database and counting them in 1 SQL statement.

Unfortunately I don't get the solution so far by my own :(.

Example:

| matnr | text  |
-----------------
| 1     | test1 |
| 2     | test2 |
| 3     | test3 |

The preferred SELECT should lead to the following result:

| matnr | text  | count |
-------------------------
| 1     | test1 | 3     |
| 2     | test2 | 3     |
| 3     | test3 | 3     |

Any ideas?

BR

[Sandra_Rossi]

What did you try?

[raymond_giuseppi]

And what's you version, many new options are available in Abap SQL recent versions

[dschiener]

@raymond_giuseppi 7.50 😞

@Sandra_Rossi 

SELECT
  FROM
    matnr_table
  FIELDS
    matnr,
    COUNT( matnr )
  INTO TABLE
    @DATA(result).


SELECT
  FROM
    matnr_table
  FIELDS
    matnr,
    ( SELECT FROM matnr FIELDS COUNT(*) ) AS count
  INTO TABLE
    @DATA(result).

[raymond_giuseppi]

For recent versions, which SQL expressions/functions did you already try?

[raymond_giuseppi]

Was the WITH already available in 7.50 (and not only from 7.51 on)

[dschiener]

None. There is no built-in SQL function that could help me out here.

No, the statement WITH is not supported :(.